SQL distinct on one column with two tables

Question

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!

select max(created_at) .... group by impressionable_id?

Marc B
– Marc B

2015-02-23 21:04:28 +00:00
Commented Feb 23, 2015 at 21:04 — Marc B
– Marc B, Commented Feb 23, 2015 at 21:04

Raging Bull · Accepted Answer · 2015-02-24 05:10:19Z

1

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;

edited Feb 24, 2015 at 5:10

answered Feb 23, 2015 at 21:05

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RKRaider Over a year ago

Thanks but this gets the records from just the one table.

Raging Bull Over a year ago

@RKRaider: That is because, I didn't know your table structure. Now I have updated my answer with a join.

RKRaider Over a year ago

This is my working solution with the slight exception of the join clause being on I.impressionable_id not user_id. I then added a where clause for the user_id.

jurgen · Accepted Answer · 2015-02-23 21:59:57Z

1

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;

edited Feb 23, 2015 at 21:59

answered Feb 23, 2015 at 21:53

jurgen

891 silver badge2 bronze badges

3 Comments

RKRaider Over a year ago

I gave this the answer as I was able to fix it adding the fix: c.id = i.impressionable_id where i.user_id =1 in lieu of c.id = i.user_id.

RKRaider Over a year ago

After careful testing this works on the first record only then it does not get the latest records for the next client ids.

jurgen Over a year ago

@RKRaider Check the screenshot: http://i.gyazo.com/bf4e470fbac69e330810ca5e5f7c9945.png I created both your tables and run the query. Are you missing something saying that it doesn't work properly ?

Collectives™ on Stack Overflow

SQL distinct on one column with two tables

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related