0

I have a use case like 

a 3
a 4
a 5
a 6
a 5
b 3
b 5
b 3

how to get the output like

a 4
a 5
b 5 
b 3

choose the hight number for a and b, but just 2 rows

that's the query I wrote now, seems it is not working

SELECT id, barcode, actualsku, inventorycount
FROM (  SELECT pallet.id                             AS id,
           pallet.barcode                        AS barcode,    
           inventoryunit.sku                     AS actualsku, 
           Count(inventoryunit.id)               AS inventorycount 
    FROM   (SELECT * 
        FROM   mft.asset 
        WHERE  container_type = 'PALLET' 
               AND location_type = 'PRIME' :: mft.location_type 
               AND is_deleted = FALSE 
               AND ( attributes ->> 'sku' :: text ) IS NOT NULL) pallet 
           LEFT OUTER JOIN (SELECT * 
                FROM   mft.asset 
                WHERE  asset_type = 'INVENTORYUNIT' :: mft.asset_type 
                       AND is_deleted = FALSE) inventoryunit 
                ON pallet.id = inventoryunit.parent_id 
    GROUP  BY inventoryunit.sku,
          pallet.id,
          pallet.barcode, 
          pallet.attributes ) test
WHERE (SELECT COUNT(*) FROM test as t
    WHERE t.actualsku = test.actualsku
        AND t.inventorycount <= test.inventorycount
        ) <= 2
ni yanwen
  • 33
  • 1
  • 7
  • How do you select the two rows? – PM 77-1 Mar 24 '17 at 00:50
  • 2
    what's the logic behind the result? – RoMEoMusTDiE Mar 24 '17 at 00:51
  • 1
    This is a duplicate of http://stackoverflow.com/questions/15969614/in-sql-how-to-select-the-top-2-rows-for-each-group – Jacob Davis-Hansson Mar 24 '17 at 00:54
  • Possible duplicate of [In SQL, how to select the top 2 rows for each group](http://stackoverflow.com/questions/15969614/in-sql-how-to-select-the-top-2-rows-for-each-group) – Ken White Mar 24 '17 at 00:56
  • Is there any query that you ever try it? – 鄭有維 Mar 24 '17 at 01:02
  • select a.col1, a.col2 from (select ROW_NUMBER() over (partition by col1 order by col2 desc) rownum, sel.* from (select 'a' col1 , 3 col2 union all select 'a' col1 , 4 col2 union all select 'a' col1 , 5 col2 union all select 'a' col1 , 6 col2 union all select 'b' col1 , 3 col2 union all select 'b' col1 , 5 col2 union all select 'b' col1 , 3 col2 ) sel ) a where a.rownum <= 2 – Vecchiasignora Mar 24 '17 at 05:05

1 Answers1

1

This is typically done using window functions:

select col1, col2 
from (
  select col1, col2, 
         row_number() over (partition by col1 order by col2 desc) as rnk
  from the_table
) t
where rnk <= 2
order by col1, col2;

Online example: http://rextester.com/WKLTSB43296