On Wednesday 23 May 2012 10:46:02 Pavel Stehule wrote: > select distinct on (s.s_registration) * > ... order by u.ud_id desc
I tried doing this but it complained about the order by. goole=# select distinct on (s.s_stock_no) s_stock_no, s_regno, s_vin, s_created, ud_id, ud_handover_date from stock s left outer join used_diary u on s.s_regno = u.ud_pex_registration where s_stock_no = 'UL15470' order by s_stock_no, ud_id desc; ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions goole=# select distinct on (s.s_stock_no) s_stock_no, s_regno, s_vin, s_created, ud_id, ud_handover_date from stock s left outer join used_diary u on s.s_regno = u.ud_pex_registration where s_stock_no = 'UL15470' order by ud_id desc; ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions goole=# > > or > > select * > from stock_details s > left join (select * from used_diary where (ud_id, > ud_registration) = (select max(ud_id), ud_registration from used_diary > group by ud_registration)) x > on s.s_registration = x.ud_registration; > This was more like what I was thinking, but I still get an error, which I don't understand. I have extracted the inner sub-select and it does only return one record per registration. (The extra criteria is just to ignore old or cancelled tax requests and doesn't affect the query) goole=# select distinct on (s.s_stock_no) s_stock_no, s_regno, s_vin, s_created, ud_id, ud_handover_date from stock s left outer join (select ud_id, ud_pex_registration, ud_handover_date from used_diary where (ud_id, ud_pex_registration) = (select max(ud_id), ud_pex_registration from used_diary where (ud_tab is null or ud_tab <> 999) and ud_created > CURRENT_DATE-'4 months'::interval group by ud_pex_registration)) udIn on s.s_regno = udIn.ud_pex_registration; ERROR: more than one row returned by a subquery used as an expression -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql