mahesh kajale mahesh kajale - 2 months ago 10
SQL Question

group by with order by query issue

I have activities table and sample data like

enter image description here

I need to fetch distinct contact_id order by created_at desc of activity_type_id 3

I have tried

select distinct contact_id
from activities
where activity_type_id = 3 order by created_at desc limit 40 offset 0


then postgres gives error like


ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ...om activities where activity_type_id = 3 order by created_at...


I solved this kind of errors and tried queries but result in not as i expect.

Group by contact_id with order by created_at desc is not giving the correct result.

like above I tried, group by, order by, distinct etc combination of queries.

Please suggest me.

I want O/P like below from above data

id | contact_id | activity_type_id | created_at
-------+------------+------------------+---------------------
718006 | 45816 | 3 | 2016-10-03 12:29:57
718005 | 45153 | 3 | 2016-10-03 12:27:01
718204 | 3491 | 3 | 2016-10-03 12:25:14
718186 | 42393 | 3 | 2016-10-03 12:23:00
718172 | 26867 | 3 | 2016-10-03 12:21:07
718171 | 45954 | 3 | 2016-10-03 12:20:09


basically all unique contact_id order by created_at desc order.

Thanks

Answer

You need to specify which contact id you care about -- because you have multiple rows.

When you use select distinct, only the columns in the select are available in the order by clause.

Hence, use group by and order by like this:

select a.contact_id
from activities a
where a.activity_type_id = 3
group by a.contact_id
order by max(a.created_at) desc
limit 40 offset 0;
Comments