Need Highest MAX on 2 variables

Question

Post reply

Need Highest MAX on 2 variables

mcwfarmer

Grasshopper

Points: 13
More actions
April 14, 2010 at 7:05 am

#231677

Hi,
I would like to retrieve the latest date and if there are 2 or more dates the same, then get the highest sequence no for each customer. Example
Cust OrderDate Seqno
ABC 1/1/2010 01
ABC 2/1/2010 01
ABC 2/1/2010 02
DEF 2/1/2010 01
DEF 3/1/2010 01
Should retrieve 2 lines. The 3rd and 5th line.
ABC 2/1/2010 02
DEF 3/1/2010 01
Using the MAX function I can get the highest date. But I don't know how to get the
highest SEQNO within the highest date.
Any help would be appreciated!!!

Viewing 3 posts - 1 through 2 (of 2 total)

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

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 1

Select Cust,MAX(OrderDate) Maxdate,MAX(SeqNo) MaxSQno

from tblname group by Cust

LutzM SSC Guru Points: 107049 More actions · Answer 2

Sasidhar Pulivarthi (4/14/2010)
Select Cust,MAX(OrderDate) Maxdate,MAX(SeqNo) MaxSQno
from tblname group by Cust

Actually, this won't give the expected result... The MAX() function is applied to each field separately. See the following sample for details: I simply added another row for cust 'ABC' on Jan. 3rd with SeqNo 01...

I also included the way I'd do it.

DECLARE @tbl TABLE(Cust CHAR(3),OrderDate DATETIME, Seqno CHAR(2))

INSERT INTO @tbl

SELECT 'ABC','1/1/2010','01' UNION ALL

SELECT 'ABC','2/1/2010','01' UNION ALL

SELECT 'ABC','2/1/2010','02' UNION ALL

SELECT 'ABC','3/1/2010','01' UNION ALL

SELECT 'DEF','2/1/2010','01' UNION ALL

SELECT 'DEF','3/1/2010','01'

SELECT Cust,MAX(OrderDate) Maxdate,MAX(SeqNo) MaxSQno

FROM @tbl GROUP BY Cust

/*CustMaxdateMaxSQno

ABC2010-03-01 00:00:00.00002

DEF2010-03-01 00:00:00.00001 */

;WITH maxCustOrder AS

(

SELECT

cust,

OrderDate,

SeqNo,

ROW_NUMBER() OVER(PARTITION BY Cust ORDER BY OrderDate DESC,SeqNo DESC ) ROW

FROM @tbl

)

SELECT

cust,

OrderDate,

SeqNo

FROM maxCustOrder

WHERE ROW=1

/*custOrderDateSeqNo

ABC2010-03-01 00:00:00.00001

DEF2010-03-01 00:00:00.00001*/

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]