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

Question

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

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — PM 77-1, Nov 16 '22 at 20:13

Philip P. · Accepted Answer · 2022-11-16T20:28:05.040

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.

score 1 · Answer 2 · edited Nov 16 '22 at 20:33

1

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

edited Nov 16 '22 at 20:33

Dale K

25,246
15
42
71

answered Nov 16 '22 at 20:27

Sean

91
4

I would want to point out that the suggested answer (both of them actually) are often significantly less performant than the `ROW_NUMBER` approach proposed by other answers. – Philip P. Nov 16 '22 at 20:31
Thank you, worked the first time. – davidtboyd Nov 16 '22 at 20:47
That's a good point, thought since the OP had first been looking at `MAX()` I did want to include the ability. `FIRST_VALUE` can have some merit if you need to work with more than one partition/order by within a single query and is good to keep in mind if you find yourself creating multiple `ROW_NUMBER() == 1` subqueries – Sean Nov 16 '22 at 20:55
I thought about this some more. Depending on the database architecture and other server features for execution plan generation may affect this, too. For example, I created a sample with the assumption of a table clustered index on an identity column, and a nonclustered index on Order. In this case, `a.Max1 = a.OpAc` had a better execution plan relative percentage 38% to 62% against `ROW_NUMBER`. We also had not asked david, if the design allows duplicate "max" values of OpAc. Which `MAX()` would return all of, and `ROW_NUMBER()` approaches could swap for `RANK()` to get multiple `1`'s – Sean Dec 01 '22 at 20:25

score 0 · Answer 3 · answered Nov 16 '22 at 20:28

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 |

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

3 Answers3