Query to pull data from column based off max value of second column-CodePudding

I have a table that has [Order], [Yield], [Scrap], [OpAc] columns. I need to pull the yield based on the max value of [OpAc].

Order	Yield	Scrap	OpAc
1234	140	0	10
1234	140	0	20
1234	130	10	30
1234	130	0	40
1234	125	5	50
1234	110	15	60
1235	140	0	10
1235	138	2	20
1235	138	0	30
1235	138	0	40
1235	138	0	50
1235	137	1	60
1235	137	0	70

Expected Results

Order	Yield
1234	110
1235	137

The query that I have tried is

select [Order], [Yield], MAX([OpAc]) as Max_OpAc
from SCRAP
GROUP BY [Order], [Yield]
order by [order]

This produces

Order	Yield	Max_OpAc
1234	110	60
1234	125	50
1234	130	40
1234	140	20
1235	137	70
1235	138	50
1235	140	10

I've tried setting up some CTE queries to break it down into separate functions but I keep getting caught at this step.

WITH CTE1 AS(
    SELECT ROW_NUMBER() OVER(PARTITION BY [Order] ORDER BY [Order],[OpAc]) AS RN , * 
    FROM SAP_SCRAP
),

This proved to be redundant due to the fact that the [OpAc] field is sequential for each step.

Thanks in advance for any help

CodePudding user response：

You almost got it!

WITH Orders_By_OpAc_Desc AS (
    SELECT
        [Order],
        [Yield].
        ROW_NUMBER() OVER (PARTITION BY [Order] ORDER BY OpAc DESC) AS [rn],
    FROM
        SCRAP
)

SELECT [Order],
       [Yield]
FROM
    Orders_By_OpAc_Desc
WHERE
    rn = 1

The trick here is ROW_NUMBER() OVER (PARTITION BY [Order] ORDER BY OpAc DESC) AS [rn]. It might be confusing to understand in SQL, but when expressed in words it's a bit clearer.

This statement takes each group of rows with the same Order value (PARTITION BY [Order]), orders each group by OpAc in descending order so that the higher OpAc values end up "on top" of the group (ORDER BY OpAc DESC), and numbers each row in the group "top" to "bottom", starting with 1 (ROW_NUMBER()).

Meaning, each row with this number set to 1 has the highest OpAc value for the OrderId.

Wrap that into a CTE and then select just the rows with this number (rn) set to 1. Voi-la.

CodePudding user response：

You definitely want the OVER (PARTITION BY) but MAX() is also an option here. You want something like:

SELECT 
    * 
FROM
(
    SELECT 
        t3.* 
        , MAX(OpAc) OVER (PARTITION BY  [Order]) max1
    FROM
    SCRAP t3
) a
WHERE
a.Max1 = a.OpAc

for MAX()

Depending on your SQL Server edition, version, and query needs, you may be able to use FIRST_VALUE() as well:

SELECT 
    DISTINCT
    t3.[Order],
    FIRST_VALUE(Yield) OVER(PARTITION BY [Order] ORDER BY OpAc DESC) Yield
FROM
SCRAP t3

CodePudding user response：

You were so close. Just missing an ORDER BY OpAc DESC in your ROW_NUMBER function.

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE orders (
  [Order] int null
  , Yield int null
  , Scrap int null
  , OpAc int null
);

INSERT INTO orders ([Order], Yield, Scrap, OpAc)
VALUES (1234,140,0,10)
  , (1234,140,0,20)
  , (1234,130,10,30)
  , (1234,130,0,40)
  , (1234,125,5,50)
  , (1234,110,15,60)
  , (1235,140,0,10)
  , (1235,138,2,20)
  , (1235,138,0,30)
  , (1235,138,0,40)
  , (1235,138,0,50)
  , (1235,137,1,60)
  , (1235,137,0,70)
 ;

Query 1:

WITH CTE1 AS (
  SELECT *
    , ROW_NUMBER() OVER(PARTITION BY [Order] ORDER BY OpAc DESC) as row_num
  FROM orders
) 
SELECT *
FROM CTE1 as c
WHERE c.row_num = 1

Results:

| Order | Yield | Scrap | OpAc | row_num |
|-------|-------|-------|------|---------|
|  1234 |   110 |    15 |   60 |       1 |
|  1235 |   137 |     0 |   70 |       1 |