Select Unique

Anon

Hello out there,

Anyone know how to run a select query that will return only one instance of a field?

In other words, my query right now selects on a field such as User, but that unique user may be in this table more than once (say multiple messages for that user). How do I pull out that row...and not all the other rows associated with that same user??

Example:
row User Msg Date
1 xxx asdfasdf 1.1.01
2 yyy asdfasdf 1.2.01
3 xxx asdfasdf 1.5.01
4 zzz asdfasdf 1.6.01

How would I state my query to return rows 1,2,4 OR 2,3,4? Not 1,2,3,4,5.

Thanks in advance!

gary

sarahk

try SELECT DISTINCT to get the list of users. But to get a particular row - well you'd have to know something about that row that is unique. That's the job of the primary key.

Say you output a list of messages displayed as hyperlinks. The user would click on it and the php script would display the message. well the hyperlink could have ?id=123 and the 123 has been generated from the database etc. The php script then says, right I have the primary key/id so I know which message to display.

hope this helps
S

[deleted]

Quite correct.

The thing to note is that SELECT DISTINCT will return the unique values of a column.

This is the 'end result' of doing some work on all the records in the table.

That means you cannot fetch any other data than the DISTINCT value unless you specify it with extra (complex) commands.

So, this is highly illegal:

SELECT DISTINCT(name), date, email FROM users;

because there may be 500 different 'emails' for one 'name'.

Ofcourse MySQL does not care about this and just returns a random row (usually the first one it finds) ... and in return we hate MySQL.

A forum, a FAQ, what else do you need?