CODEWITHSUNDEEP
sqlmysql
Maxxon
Maxxon

Reputation: 333

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

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?

Upvotes: 19

Views: 61467

Answers (2)

yvoyer
yvoyer

Reputation: 7516

Maybe you could exclude duplicate user using GROUP BY.

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

Upvotes: 4

Nanne
Nanne

Reputation: 64429

Yes. "DISTINCT".

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

Upvotes: 39

Related Questions