MaxDate and Second to max date

Question

Post reply

MaxDate and Second to max date

sql_learner29

SSCrazy Eights

Points: 9107
More actions
October 26, 2009 at 9:28 am

#212971

Hi,
I have written a query to get the Max Date for each row. Now I need to find the second to max date from the table, but I am unable to get that...
I just want last second maxdate from a table as i have to use it in my where clause..Is there any way to get that... using query
Thanks in advance
[font="Comic Sans MS"]
---------------------------------------------------
Thanks [/font]

Viewing 7 posts - 1 through 6 (of 6 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Use row_number().

;with TopDates as

(select row_number() over (order by DateColumn desc) as Row, *

from dbo.MyTable)

select *

from TopDates

where Row<=2;

Or use Top (2).

select top 2 *

from dbo.MyTable

order by DateColumn desc;

Depends on what you want to do with the data.

- 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

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 2

Thanks, but I have many records for each date, I cant use this.

For ex :

10/23/09 -i have 1000 records

10/22/09 - i have 3000 records

I need max date -1 , but want to exclude holidays and weekends.

Thanks

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

Do you have a calendar table of some sort that designates holidays and/or weekends?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Adam A Old Hand Points: 385 More actions · Answer 4

How about using a group by and row_number in a join?

CREATE TABLE #DateTable(Date1 datetime)

INSERT INTO #DateTable ( Date1 ) VALUES ( '2009-10-26')

INSERT INTO #DateTable ( Date1 ) VALUES ( '2009-10-25')

INSERT INTO #DateTable ( Date1 ) VALUES ( '2009-10-24')

INSERT INTO #DateTable ( Date1 ) VALUES ( '2009-10-23')

INSERT INTO #DateTable ( Date1 ) VALUES ( '2009-10-22')

SELECT *

FROM #DateTable dt

JOIN (SELECT

Date1,

ROW_NUMBER() OVER (ORDER BY date1 DESC) AS RowNumber

FROM #DateTable dt1

GROUP BY Date1

) dt2

ON dt.Date1 = dt2.Date1

AND dt2.RowNumber = 2

DROP TABLE #DateTable

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

Is this what you're looking for?

-- the ctes get you the date

;with cte1 (DateColumn) as (select distinct dateadd(day,datediff(day,0,DateColumn,0) from dbo.MyTable)

,cte2 (DateColumn,rowID) as (select DateColumn,row_number() over(order by DateColumn) from cte1

-- WHERE (omit holidays and weekends))

,cte3 (DateColumn1,DateColumn2) as (select DateColumn, dateadd(day,1,DateColumn) from cte2 where rowID = 2)

-- this query gets you all rows for that date

select *

from dbo.MyTable m

cross join cte3 c

where M.dateColumn >= c.DateColumn1 and m.dateColumn < c.DateColumn2

Warning: Above code was typed freehand and untested.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

If you put a partition by on your the row_number function it will return the same row_number for the same dates, assuming you don't store the time portion of the date.

Can you post some sample data and desired output from the sample data?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question