Using Max() function to select group values

问题:

I've got a table like this:

     SKU    ITEM      VALUE
    1503796 1851920 0,9770637
    1503796 1636691 0,9747891
    1503796 1503781 0,9741025
    1503796 3205763 0,9741025
    1503801 1999745 0,9776622
    1503801 1999723 0,9718825
    1503801 3651241 0,9348839
    1503801 1773569 0,9331309
    1503811 1439825 0,97053134
    1503811 1636684 0,96297866
    1503811 1636671 0,96003973
    1503811 1600553 0,9535771
    1503818 1636708 0,9440251
    1503818 1636709 0,9440251
    1503818 1779789 0,9423958
    1503818 3322310 0,9369579

I need to get output like this (grouped with max value):

SKU      ITEM     VALUE
1503796 1851920 0,9770637
1503801 1999745 0,9776622
1503811 1439825 0,97053134
1503818 1636708 0,9440251

tried to use smth like this:

select SKU, ITEM, VALUE from import
where value=(select max(value) from import )

But it select only one row with max value. How to rewrite query?

回答1:

Rank the records with ROW_NUMBER, so that the max value for an sku gets #1. Then keep only those records ranked #1.

select sku, item, value
from
(
  select 
    mytable.*
    row_number() over (partition by sku order by value desc) as rn
  from mytable
)
where rn = 1;

For SKU 1503818 you will get either of these two:

1503818 1636708 0,9440251
1503818 1636709 0,9440251

If you want a particular one (e.g. the one with the higher item number) then add this criteria to Row_Number's ORDER BY clause.

As to the query you tried yourself: You should be looking for sku-value pairs instead:

select SKU, ITEM, VALUE from import
where (sku,value) in (select sku, max(value) from import group by sku);

In case of a tie, as with SKU 1503818, this query will get you both records, however.

回答2:

Use a common table expression

WITH CTE AS 
(SELECT SKU,ITEM,VALUE,
ROW_NUMBER() OVER (PARTITION BY SKU ORDER BY value DESC)as maxvalue 
FROM import)
SELECT SKU,ITEM,VALUE FROM CTE WHERE maxvalue=1

回答3:

You can use ... KEEP ( DENSE_RANK ... ) with an aggregation function to get the maximum of a different row:

SELECT SKU,
       MAX( ITEM ) KEEP ( DENSE_RANK LAST ORDER BY VALUE ASC ) AS ITEM,
       MAX( VALUE ) AS VALUE
FROM   IMPORT
GROUP BY SKU;