Display the record using GroupBY

Question

Display the record using GroupBY

souravagar

Newbie

Points: 5
More actions
August 3, 2015 at 12:10 pm

#398840

I would like to display all the products with maximum SeqNo from the table below:
TABLE
ProductIDSeqNoBalance
111215
11135
111420
111510
12115
1212100
121325
121445
OUTPUT
ProductIDSeqNoBalance
111510
121445

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

what have you tried so far?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Check this for examples on how to solve the problem:

http://stackoverflow.com/questions/10591044/how-write-sql-to-select-the-maximum-value-in-each-group

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 3

Use this

SELECT t.ProductID,tmp.MaxSeqNo,t.Balance

FROM YourTable t

JOIN

( SELECT ProductID , MAX(SeqNo) [MaxSeqNo]

FROM YourTable

GROUP BY ProductID

) tmp

ON tmp.ProductID = t.ProductID

AND tmp.MaxSeqNo = t.SeqNo

Igor Micev,My blog: www.igormicev.com