Use MAX with a Group By

Question

Use MAX with a Group By

Johnathan Simpson 586

I want to return MAX() of the lines for an order. Even though In my query I do Max(ln) it still returns the line number.

How would this query need to be set-up so that MAX() returns MAX()

Create Table #Test
(
    ordernumber varchar(100)
    ,linenumber int
    ,item varchar(100)
)

Create Table #SaleData
(
    ordernumber varchar(100)
    ,salesperson varchar(250)
)

Insert Into #Test Values
('ABC-123', 1, 'shoe'), ('ABC-123', 2, 'sock'), ('ABC-123', 3, 'cap'), ('ABC-123', 4, 'mit')
,('RST-111', 1, 'shoe'), ('RST-111', 2, 'shirt'), ('EEA-123', 1, 'ball')

Insert Into #SaleData Values
('ABC-123', 'Mitchell'), ('RST-111', 'Jason'), ('EEA-123', 'Bob')

Select
sd.Ordernumber
,MAX(t.LineNumber) As Maxln
,t.item
,t.linenumber
FROM #SaleData sd
Join #Test t
ON t.ordernumber = sd.ordernumber
GROUP BY sd.ordernumber, t.linenumber, t.item

What i'd like to have returned for max is below
ABC-123 4
EEA-123 1
RST-111 2

but i also need to return the other data.

This is using ms sql server 2016

Yitzhak Khabinsky 27,106 Reputation points

2021-05-11T19:50:54.44+00:00

@Johnathan Simpson ,

What about https://learn.microsoft.com/en-us/answers/questions/389830/select-query-asstiance.html
EchoLiu-MSFT 14,626 Reputation points

2021-05-13T07:12:01.47+00:00

Please also remember to accept the answers if they helped.
Your action would be helpful to other users who encounter the same issue and read this thread.
EchoLiu-MSFT 14,626 Reputation points

2021-05-14T09:25:50.993+00:00

Could you have any update?

2 answers

Your answer

Yitzhak Khabinsky 27,106 Reputation points

2021-05-11T19:50:54.44+00:00

@Johnathan Simpson ,

What about https://learn.microsoft.com/en-us/answers/questions/389830/select-query-asstiance.html
EchoLiu-MSFT 14,626 Reputation points

2021-05-13T07:12:01.47+00:00

Please also remember to accept the answers if they helped.
Your action would be helpful to other users who encounter the same issue and read this thread.
EchoLiu-MSFT 14,626 Reputation points

2021-05-14T09:25:50.993+00:00

Could you have any update?

Answer 1

Tom Cooper 8,501

If I understand what you want, then
Select
sd.Ordernumber
,MAX(t.LineNumber) Over(Partition By sd.Ordernumber) As Maxln
,t.item
,t.linenumber
FROM #SaleData sd
Join #Test t
ON t.ordernumber = sd.ordernumber

If that does not give you want you want, please show us what you want your query to return given your sample data.
Tom

Johnathan Simpson 586 Reputation points

2021-05-11T18:46:55.12+00:00

@Tom Cooper - that is exactly what I needed. Do you have time to explain how adding in the Over() solved it?
Tom Cooper 8,501 Reputation points

2021-05-11T20:11:46.217+00:00

When you use GROUP BY, every column in your SELECT list must either be in the GROUP BY items, or be an aggregate function (like MAX(), AVG(), SUM, etc) or be a constant.

But when you use an aggregate function with the OVER clause (that's called a windowed function), you don't need a GROUP BY. That means you can have both grouped items and non-grouped items in the same SELECT clause. Windowed functions provide an efficient way to do many tasks.

The documentation for the OVER clause is at https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15
An of example of how to use them is understanding-the-over-clause

Answer 2

Hi @Johnathan Simpson ,

Please also check:

    ;WITH cte  
     as( Select sd.Ordernumber,MAX(t.LineNumber) As Maxln  
         FROM #SaleData sd  
         Join #Test t ON t.ordernumber = sd.ordernumber  
         GROUP BY sd.ordernumber)  
       
     Select * from cte c  
     Join #Test t  
     ON t.ordernumber = c.ordernumber

Output:

If you have any question, please feel free to let me know.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

Use MAX with a Group By

2 answers

Your answer