Sort by id desc on multiple columns distinct postrges

Question

SELECT impressions.* 
FROM impressions 
WHERE impressions.user_id = 2 
  AND impressions.action_name = 'show' 
  AND (impressions.message IS NOT NULL) 
GROUP BY impressionable_id, impressionable_type

I'd like to select from the table all last impressions that are unique on impressionable_id and impresssionable_type ordering by descending id and get the last 10

To explain this further

id, impressionabale_type, impressionable_id, action_name

50012, assignment, 2, show
50011, assignment, 1, show
50010, person, 1, show
50009, assignment, 1, show
50008, person, 5, show
50007, person, 4, show
50006, person, 3, show
50005, person, 1, show
50004, person, 1, show
50003, person, 2, show
50002, person, 2, show
50001, person, 1, show
50000, person, 1, show

Ideally I want this

50012, assignment, 2, show
50011, assignment, 1, show
50010, person, 1, show
50008, person, 5, show
50007, person, 4, show
50006, person, 3, show
50003, person, 2, show

I have tried distinct and group by but my sql knowledge is fair at best.

I get

PG::GroupingError: ERROR:  column "impressions.id" must appear in the GROUP BY clause or be used in an aggregate function

Can someone shed some light please

Answer 1

Maybe thi will suit your needs:

SELECT t2.*
FROM (
    SELECT DISTINCT impressionable_id, impressionabale_type
    FROM impressions
    WHERE impressions.action_name = 'show'
) t1, LATERAL (
    SELECT *
    FROM impressions
    WHERE (t1.impressionable_id,t1.impressionabale_type) = (impressionable_id,impressionabale_type)
    ORDER BY id DESC
    LIMIT 1
) t2
ORDER BY id DESC
LIMIT 10

This will find all unique combinations of impressionable_id and impressionable_type and for each of them will find the row with the largest id in a LATERAL subquery.