I'm using Microsoft SQL Server and have a table like this:
| DATE | ITEM | BUYER | QTY_BUY |
|---|---|---|---|
| 2022-01-01 | ITEM A | TOMMY | 5 |
| 2022-01-01 | ITEM A | BENNY | 3 |
| 2022-01-01 | ITEM A | ANDY | 1 |
| 2022-01-01 | ITEM A | JOHN | 8 |
| 2022-01-01 | ITEM B | TOMMY | 2 |
| 2022-01-01 | ITEM B | BENNY | 10 |
| 2022-01-01 | ITEM B | ANDY | 3 |
| 2022-01-01 | ITEM B | JOHN | 6 |
| 2022-01-02 | ITEM A | TOMMY | 3 |
| 2022-01-02 | ITEM A | BENNY | 0 |
| 2022-01-02 | ITEM A | ANDY | 5 |
| 2022-01-02 | ITEM A | JOHN | 6 |
I want to show top buyer and min buyer group by date and item, so it will look like:
| DATE | ITEM | TOP_BUYER | TOP_QTY | MIN_BUYER | QTY_MIN |
|---|---|---|---|---|---|
| 2022-01-01 | ITEM A | JOHN | 8 | ANDY | 1 |
| 2022-01-01 | ITEM B | BENNY | 10 | TOMMY | 2 |
| 2022-01-02 | ITEM A | JOHN | 6 | BENNY | 0 |
Please help me to do that, I try so many trick but cannot reach it. Thanks in advance
CodePudding user response：
We can handle this requirement using ROW_NUMBER:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE, ITEM ORDER BY QTY_BUY) rn1,
ROW_NUMBER() OVER (PARTITION BY DATE, ITEM ORDER BY QTY_BUY DESC) rn2
FROM yourTable
)
SELECT DATE, ITEM,
MAX(CASE WHEN rn2 = 1 THEN BUYER END) AS TOP_BUYER,
MAX(CASE WHEN rn2 = 1 THEN QTY_BUY END) AS TOP_QTY,
MAX(CASE WHEN rn1 = 1 THEN BUYER END) AS MIN_BUYER,
MAX(CASE WHEN rn1 = 1 THEN QTY_BUY END) AS QTY_MIN
FROM cte
GROUP BY DATE, ITEM
ORDER BY DATE, ITEM;
CodePudding user response：
The solution is to use first_value partition over
This query was tested inside SQL Server
select distinct [date], Item
, FIRST_VALUE(buyer) OVER (partition by [date], item ORDER BY qty_buy desc) AS Top_Buyer
, FIRST_VALUE(qty_buy) OVER (partition by [date], item ORDER BY qty_buy desc) AS Top_Qty
, FIRST_VALUE(buyer) OVER (partition by [date], item ORDER BY [date], item, qty_buy asc) AS Min_Buyer
, FIRST_VALUE(qty_buy) OVER (partition by [date], item ORDER BY [date], item, qty_buy asc) AS Qty_Min
from testtable