sql - How to select the maximum of each type -
i have table describes how many fish, veg, or meat in each tray.
tray | qty | type -------+-----+------- 1 | 5 | fish 2 | 6 | veg 2 | 2 | fish 2 | 5 | meat 3 | 8 | veg 3 | 3 | fish 3 | 9 | meat 4 | 10 | meat
lets call table r (it sub table created in query).
what want table says tray has highest number of each type this:
type | tray -------+------ fish | 1 veg | 3 meat | 4
i tried write following query
select type type1, tray (select ... bla bla) r r.qty in (select max(qty) r type = type1);
the error r doesn't exist, how solve this?
i approach using window functions:
select r.type, r.tray (select r.*, row_number() on (partition type order qty desc) seqnum r ) r seqnum = 1;
Comments
Post a Comment