[SOLVED] MySQL DISTINCT Question

[Bricktop]

Hi Guys and Gals,

 

I wonder if the following is possible;  I have the following query:

 

$sql = mysql_query("SELECT DISTINCT firstname, lastname, email, telephone, FROM users ORDER BY surname ASC");

 

This works great, and as there are several people entered into the DB more than once this stops them appearing more than once when the data is outputted.

 

However, I now would like to select the "ID" column from the same "USERS" table, but obviously this means that those duplicated users show up again because each ID field is a DISTINCT value.

 

Am I able to keep the above DISTINCT results but also extract the ID from them?

 

Hope this makes sense and any help greatly appreciated.

 

Thanks

[revraz]

I'm not sure why you want to allow duplicate entries to start with?

[Bricktop]

It's quite a long story and it's a large database with a lot of duplicate entries.  Anyone know how I can achieve the above?

 

Thanks

[lonewolf217]

my only guess would be two queries.  one with SELECT DISTINCT to get the unique usernames, and another with a global SELECT to get all the username -> ID references

[revraz]

How about solving the actual problem and deleting the Duplicates so you don't have to worry about it?

 

How are you going to resolve which ID is the real one?

 

It's quite a long story and it's a large database with a lot of duplicate entries.  Anyone know how I can achieve the above?

 

Thanks

[revraz]

Look at this thread for a easy way to delete dupes.

 

http://www.phpfreaks.com/forums/index.php/topic,233331.0.html

[Bricktop]

OK, thanks revrez, I'll have a look.  Thanks lonewolf but I'd already tried your suggestion but like revraz says the ID's don't match up to the correct entries.

 

Anyway, thanks for your replies.