help with select distinct

richie

I have this table:

id | login

12805 | mike
12861 | brown
12805 | richard
12861 | james

I issue this query:
SELECT DISTINCT id, login from tablename

The result returns all rows in the table, instead of dropping the duplicate id's.

For some reason, the SELECT DISTINCT is being ignored.

What am I doing wrong?

I have mysql version 3.23.54 on linux.

TIA,
Richie.

Deany

I'm no expert, but this works for me:

$result = mysql_query("SELECT DISTINCT name,surname,web FROM members WHERE web != ' ' ORDER BY surname ASC");

This selects distinct results from the members table where the web field (containing URLS) is not (!=) blank. The list generated is also put in ASCending order by surname.

It might be helpful for you?
I'm not sure but in your case try...

$result = mysql_query("SELECT DISTINCT id,login FROM tablename WHERE 1 ORDER BY login ASC");

Hope this helps :queasy: Just getting the hang of this myself.

nemonoman

Each of these rows is DISTINCT:
12805 | mike
12861 | brown
12805 | richard
12861 | james

SELECT DISTINCT looks at the COMPLETE row being selected.

You could say:
SELECT DISTINCT id FROM tablename

and return 2 rows only.

Roger_Ramjet

Why are your ids duplicated?

richie

Thanks to everybody.

nemonoman, you answered my question. I was trying to eliminate rows with duplicate id's, but I didn't realize that all columns count in SELECT DISTINCT.

I guess I can do:

SELECT DISTINCT id

then iterate through the results and

SELECT *

Richie.

Roger_Ramjet

How you gonna do that, you'll still have the duplicate ids problem?