question

InigoMontoya-1790 avatar image
0 Votes"
InigoMontoya-1790 asked EchoLiu-msft commented

Use MAX with a Group By

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

sql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

Could you have any update?

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @InigoMontoya-1790,

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:
95746-image.png


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.




image.png (9.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomCooper-6989 avatar image
1 Vote"
TomCooper-6989 answered TomCooper-6989 commented

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@TomCooper-6989 - that is exactly what I needed. Do you have time to explain how adding in the Over() solved it?

0 Votes 0 ·

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://docs.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




1 Vote 1 ·