CODEWITHSUNDEEP
sqlpostgresql
RKRaider
RKRaider

Reputation: 17

SQL distinct on one column with two tables

I am having trouble getting this query correct. I am using Postgres and trying to query the last 15 clients viewed by a user.

Table client
Columns id, name...

Table impressions
Columns imressionable_id, user_id, created_at ...

The impressionable_id is equal to the client_id and the tables can be joined on this field.

I need to get a list of the last 15 DISTINCT client names viewed by the user and ordered DESC by the created_at date. The problem is currently a query of the impressions table will produce many duplicate entries but with unique created_at timestamp as each time you view a client it creates a new impressions record.

So, a query of

select impressions.impressionable_id, created_at from impressions where user_id = 1 order by created_at DESC

will produce:

  impressionable_id   created_at
        1            2014-11-14 21:44:47.705167 
        1            2014-11-14 21:32:15.411488 
        3            2014-11-14 18:43:26.020719 
        1            2014-11-14 18:42:15.974442
        5            2014-11-14 18:41:10.609617 
        3            2014-10-29 20:53:01.383896

I need a query that will give me:

1    John Doe     2014-11-14 21:44:47.705167 
3    Jay Smith    2014-11-14 18:43:26.020719
5    Tim Jones    2014-11-14 18:41:10.609617

and so on. Any help is greatly appreciated!

Upvotes: 0

Views: 72

Answers (2)

Raging Bull
Raging Bull

Reputation: 18767

Use MAX() and GROUP BY:

select I.impressionable_id,c.name, MAX(I.created_at) as created_at 
from impressions JOIN
     client c ON c.id = I.user_id
group by impressionable_id,c.name
order by created_at DESC
limit 15;

Upvotes: 1

jurgen
jurgen

Reputation: 89

SELECT y.* FROM 
(
    SELECT 
        DISTINCT ON (x.id) x.id, x.name, x.created_at 
    FROM (SELECT c.id, c.name, i.created_at FROM clients c RIGHT JOIN impressions i ON c.id = i.user_id) x
) y 
ORDER BY y.created_at DESC LIMIT 15;

Upvotes: 1

Related Questions