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

Popular posts from this blog

java - SSE Emitter : Manage timeouts and complete() -

jquery - uncaught exception: DataTables Editor - remote hosting of code not allowed -

java - How to resolve error - package com.squareup.okhttp3 doesn't exist? -