SQL: How to select rows from a table while ignoring the duplicate field values?

Question

How to select rows from a table while ignoring the duplicate field values?

Here is an example:

id          user_id          message

1           Adam             "Adam is here."
2           Peter            "Hi there this is Peter."
3           Peter            "I am getting sick."
4           Josh             "Oh, snap. I'm on a boat!"
5           Tom              "This show is great."
6           Laura            "Textmate rocks."

What i want to achive is to select the recently active users from my db. Let's say i want to select the 5 recently active users. The problem is, that the following script selects Peter twice.

mysql_query("SELECT * FROM messages ORDER BY id DESC LIMIT 5 ");

What i want is to skip the row when it gets again to Peter, and select the next result, in our case Adam. So i don't want to show my visitors that the recently active users were Laura, Tom, Josh, Peter, and Peter again. That does not make any sense, instead i want to show them this way: Laura, Tom, Josh, Peter, (skipping Peter) and Adam.

Is there an SQL command i can use for this problem?

Nanne · Accepted Answer · 2011-01-13 16:49:25Z

39

Yes. "DISTINCT".

 SELECT DISTINCT(user_id) FROM messages ORDER BY id DESC LIMIT 5

answered Jan 13, 2011 at 16:49

Nanne

64.4k16 gold badges122 silver badges166 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

coreyward Over a year ago

This won't work if he is trying to get the messages as well. DISTINCT discriminates based on all returned columns.

Nanne Over a year ago

correct! But from the mention of names i'd say he wants the names. You can add it as a subquery for complete rows?

Maxxon Over a year ago

Thanks for the info coreyward. Great to know. What to use if i want to select the messages too?

Maxxon Over a year ago

Yes, this case I only wanted to select the names, since I want to know which users were recenlty active. Thanks Nanne.

Maxxon Over a year ago

But it would be great to know how to select the messages too at the same time? Any solutions?

|

yvoyer · Accepted Answer · 2011-01-13 16:50:42Z

4

Maybe you could exclude duplicate user using GROUP BY.

SELECT * FROM messages GROUP BY user_id ORDER BY id DESC LIMIT 5;

answered Jan 13, 2011 at 16:50

yvoyer

7,5465 gold badges35 silver badges37 bronze badges

4 Comments

Maxxon Over a year ago

What are the differences between grounping and distinct?

coreyward Over a year ago

GROUP BY will group all rows on a column. DISTINCT will filter out any rows where all of the returned columns are identical. In most cases, GROUP BY is faster. In this case, it'll also mean you can pull down the messages in the same query if you want to.

Nanne Over a year ago

Grouping wil merge everything for that field. This means you should use an aggregate funciton on the other fields. AFAIK not very usefull for you situation

Cesar Over a year ago

@coreyward I'm sorry, MySQL sux, in sybase doesn't work and in mysql really works, sorry about that. RTFM for me!

Collectives™ on Stack Overflow

SQL: How to select rows from a table while ignoring the duplicate field values?

2 Answers 2

7 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related