CODEWITHSUNDEEP
mysqlsql
sdexp
sdexp

Reputation: 776

How to only get DISTINCT rows from a JOIN query: MySQL

Two tables. Table one is users. Table two is user events. One user has many events. I just want to get a list of the users with the most recent events. I can obviously set it to select more rows than I need and filter out the duplicates but I really want to just select the exact number of rows I need in the SQL.

This SQL query gives duplicates of a.name where one user has had more than one event recently. I thought that DISTINCT would prevent duplicates. What am I doing wrong? How do I just get one row for each user?

SELECT DISTINCT (a.name), b.date , a.id
FROM `users` a
INNER JOIN events b
ON a.id = b.userid 
WHERE now() > b.date 
ORDER BY b.date DESC, a.id DESC
LIMIT 30;

Table users...

id name

Table events...

id userid date

I don't need to select the b.date value but this is needed for DISTINCT to work because it is in the ORDER BY part, I believe.

Upvotes: 0

Views: 51

Answers (1)

sticky bit
sticky bit

Reputation: 37527

You could GROUP BY the user and only take the maximum date (or the minimum, if you want the oldest of the events).

SELECT a.name,
       max(b.date) date,
       a.id
       FROM users a
            INNER JOIN events b
                       ON a.id = b.userid 
       WHERE now() > b.date 
       GROUP BY a.id,
                a.name
       ORDER BY max(b.date) DESC,
                a.id DESC
       LIMIT 30;

DISTINCT won't help you, when there are different date values for a user. Those records are distinct because of the different date values, though sharing a user.

Upvotes: 1

Related Questions