Return records with Max Sequence Number

Question

Return records with Max Sequence Number

Welsh Corgi

SSC Guru

Points: 116520
More actions
March 29, 2012 at 8:06 am

#266376

I need to return the records with the Maximun Sequence Number only.
This is some sample Detail Data:
CompanyCategoryAcctgDateSequenceAmount
85WritPrem11201273.00
85WritPrem112012-73.00
85WritPrem112011-234.00
85WritPrem112011234.00
85WritPrem112011234.00
85WritPrem11201174.00
85WritPrem11201113.00
85WritPrem11201142.00
85WritPrem112011264.00
85WritPrem112011246.00
85WritPrem112011184.00
85WritPrem112011-184.00
85WritPrem112011184.00
85WritPrem112011254.00
85WritPrem112011-254.00

I know that this is not right. I have done this before, what is the correct way to do this?
SELECT Company, Category, AcctgDate, MAX(DISTINCT(SequenceNumber) AS MaxSeq,
SUM(Amount) AS SumAmount
GROUP BY Company,Category, AcctgDate
HAVING Company = '85'
AND Category = 'TotalSales'
AND AcctgDate = '11201'
-- HAVING MAX(DISTINCT(SequenceNumber) ??
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 9 posts - 1 through 8 (of 8 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

I just need to add a subquery where SequenceNumbe IN MAX(SequenceNumber) and do the groupping.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

3,000 visits and you haven't learnt that the best way to get answers to your questions is to provide consumable sample data rather than a non-consumable table?

SELECT Company, Category, AcctgDate, Sequence, Amount

INTO #yourSampleData

FROM (VALUES(85,'WritPrem',11201,2,73.00),

(85,'WritPrem',11201,2,-73.00),

(85,'WritPrem',11201,1,-234.00),

(85,'WritPrem',11201,1,234.00),

(85,'WritPrem',11201,1,74.00),

(85,'WritPrem',11201,1,13.00),

(85,'WritPrem',11201,1,42.00),

(85,'WritPrem',11201,1,264.00),

(85,'WritPrem',11201,1,246.00),

(85,'WritPrem',11201,1,184.00),

(85,'WritPrem',11201,1,-184.00),

(85,'WritPrem',11201,1,184.00),

(85,'WritPrem',11201,1,254.00),

(85,'WritPrem',11201,1,-254.00)) a(Company,Category,AcctgDate,Sequence,Amount);

Here's one way to do what you want.

SELECT Company, Category, AcctgDate, Sequence, Amount

FROM (SELECT Company, Category, AcctgDate, Sequence, Amount,

MAX(Sequence) OVER() AS maxSequence

FROM #yourSampleData) a

WHERE maxSequence = Sequence;

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 3

Here's another way - I've also created the data for you as well

CREATE TABLE #TEMP

(Company INT,Category VARCHAR(50),AcctgDate INT,Sequence INT,Amount MONEY)

INSERT INTO

#TEMP

SELECT 85,'WritPrem',11201,2,73.00 UNION ALL

SELECT 85,'WritPrem',11201,2,-73.00 UNION ALL

SELECT 85,'WritPrem',11201,1,-234.00 UNION ALL

SELECT 85,'WritPrem',11201,1,234.00 UNION ALL

SELECT 85,'WritPrem',11201,1,74.00 UNION ALL

SELECT 85,'WritPrem',11201,1,13.00 UNION ALL

SELECT 85,'WritPrem',11201,1,42.00 UNION ALL

SELECT 85,'WritPrem',11201,1,264.00 UNION ALL

SELECT 85,'WritPrem',11201,1,246.00 UNION ALL

SELECT 85,'WritPrem',11201,1,184.00 UNION ALL

SELECT 85,'WritPrem',11201,1,-184.00 UNION ALL

SELECT 85,'WritPrem',11201,1,184.00 UNION ALL

SELECT 85,'WritPrem',11201,1,254.00 UNION ALL

SELECT 85,'WritPrem',11201,1,-254.00

SELECT

Company

,Category

,AcctgDate

,A.Sequence

,Amount

FROM

#TEMP AS A

INNER JOIN( SELECT

Sequence

,ROW_NUMBER() OVER ( ORDER BY Sequence DESC) AS RowNum

FROM

#TEMP

) AS B ON A.Sequence = B.Sequence AND B.RowNum = 1

DROP TABLE #TEMP

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 4

Cadavre (3/29/2012)
3,000 visits and you haven't learnt that the best way to get answers to your questions is to provide consumable sample data rather than a non-consumable table?
SELECT Company, Category, AcctgDate, Sequence, Amount
INTO #yourSampleData
FROM (VALUES(85,'WritPrem',11201,2,73.00),
(85,'WritPrem',11201,2,-73.00),
(85,'WritPrem',11201,1,-234.00),
(85,'WritPrem',11201,1,234.00),
(85,'WritPrem',11201,1,234.00),
(85,'WritPrem',11201,1,74.00),
(85,'WritPrem',11201,1,13.00),
(85,'WritPrem',11201,1,42.00),
(85,'WritPrem',11201,1,264.00),
(85,'WritPrem',11201,1,246.00),
(85,'WritPrem',11201,1,184.00),
(85,'WritPrem',11201,1,-184.00),
(85,'WritPrem',11201,1,184.00),
(85,'WritPrem',11201,1,254.00),
(85,'WritPrem',11201,1,-254.00)) a(Company,Category,AcctgDate,Sequence,Amount);
Here's one way to do what you want.
SELECT Company, Category, AcctgDate, Sequence, Amount
FROM (SELECT Company, Category, AcctgDate, Sequence, Amount,
MAX(Sequence) OVER() AS maxSequence
FROM #yourSampleData) a
WHERE maxSequence = Sequence;

Beaten to it!!

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Mark Cowne One Orange Chip Points: 26979 More actions · Answer 5

Another way

SELECT TOP 1 WITH TIES

Company, Category, AcctgDate, Sequence, Amount

FROM #yourSampleData

ORDER BY Sequence DESC

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Yea, I was having a bit of a problem seeing the sequence in the original post. Doesn't really help when column headers and data don't line up. The only other thing missing, besides having the data in a readily consumable format, was the expected results. I for one am more visual in my problem solving and having that would be beneficial. Plus, it gives you something to test against.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

ok, I'm sorry about the poor post.:blush:

I had to come up with aliases because the information is confidential but you have given me ideas to present you with what you need to test.

Thanks to all of you for the great responses.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

GSquared SSC Guru Points: 260824 More actions · Answer 8

If you need the top value per company, then Max and Group By is the way to go. If you simply need the highest value (ties?) then Top 1 and Order By is the easiest. Can't be sure from the original post which one of those you're looking for.

A third option is Cross Apply. Pull all the companies from the table they are stored in, then select the Top 1 from the sub-table with the sequence numbers in for each of them. That would get you the whole row from that table for each company, which the aggregate functions won't.

Something like:

select *

from dbo.CompaniesTable

cross apply (select top 1 * from dbo.SubTable where CompanyID = CompaniesTable.ID order by SequencerColumn desc) as Sub ;

That's easier than pulling max values in a sub-query and using those in a Where clause, in my opinion.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Return records with Max Sequence Number

Cookies on SQLServerCentral