Sql Query to show distinct records

Question

I have a SQL query and I am trying to display only distinct records like one post of each user order by latest post desc. How to do that? I tried group by and it failed.

SELECT jca.id, ju.name, jca.actor, jca.title as title, jca.created as postedDate FROM community_activities jca left join users ju on jca.actor=ju.id left join community_users jcu on jcu.userid=ju.id ORDER BY jca.id, jca.postedDate DESC LIMIT 0 , 50; id name actor title 200 chandra 12 hello man 201 chandra 12 hey man 202 shayam 13 hello hero 203 chandra 12 hello yoyo 204 kokila 16 yep 205 chandra 12 hello y 206 chandra 12 hello abc

These records are all distinct, where are you seeing dupes records? You could use the DISTINCT keyword after the SELECT, but I don't see any dupes based on your sample data — Sparky
– Sparky, Commented Jun 7, 2014 at 7:11
As @Sparky says, they are already distinct. You have, for example, different titles for each user. If you want to show only one per use, which title do you want to show? The latest one? Or just any one...? — wwkudu
– wwkudu, Commented Jun 7, 2014 at 7:15
DISTINCT will not work in this case. It can be applied on one column for distinct values. If you are selecting multiple columns, it will return DISTINCT ROWS. — Ricky
– Ricky, Commented Jun 7, 2014 at 7:18
Names are not distinct in my above result set . I am trying to display one latest post of each user — Kurkula
– Kurkula, Commented Jun 7, 2014 at 7:21

FuzzyTree · Accepted Answer · 2014-06-07 08:30:40Z

To display only the latest post of each user, create a derived table that consists of only the latest post id of each user and join community_activities to that table, so only those results will be displayed.

SELECT jca.id, ju.name, jca.actor, jca.title as title, jca.created as postedDate FROM community_activities jca JOIN (SELECT MAX(id) max_id FROM community_activities GROUP BY actor) t1 on t1.max_id = jca.id LEFT JOIN users ju on jca.actor=ju.id LEFT JOIN community_users jcu on jcu.userid=ju.id ORDER BY jca.id, jca.postedDate DESC LIMIT 0 , 50;

Result set is not sorted based on posted date. I think issue might be at jca.postedDate DESC
@Chandana is (author,created) unique or could there be multiple rows with the same (author,created)?
I am trying to show one record for one author. Author might post multiple post but only latest post by author should be shown.

Tuğrul · Accepted Answer · 2014-06-07 07:26:15Z

SELECT MAX(jca.id), -- just selects maximum of each column , DISTINCT ju.name, -- max() may be wrong for your scenario . MAX(jca.actor), MAX(jca.title) as title, MAX(jca.created) as postedDate FROM community_activities jca left join users ju on jca.actor=ju.id left join community_users jcu on jcu.userid=ju.id GROUP BY ju.name;

output:

202 shayam 13 hello hero 204 kokila 16 yep 206 chandra 12 hello abc

It throws below error. You have an error in your SQL syntax near 'distinct ju.name, MAX(jca.actor), MAX(jca.title) as title, MAX(jca.c' at line 2

Caleb Palmquist · Accepted Answer · 2014-06-07 18:13:01Z

SELECT jca.id, ju.name, jca.actor, jca.title AS title, MAX(jca.created) AS postedDate FROM community_activities jca LEFT JOIN users ju ON (jca.actor = ju.id) LEFT JOIN community_users jcu ON (jcu.userid = ju.id) ORDER BY jca.created DESC

The above query displays only one latest row. I am trying to get one latest record(order by created) for each user.

Collectives™ on Stack Overflow

Sql Query to show distinct records

3 Answers 3

3 Comments

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Related