Nth Max Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Nth Max Date

Author

Topic

annamalai
Starting Member

5 Posts

Posted - 2007-04-03 : 04:54:02

Hai friends.

i want to get the maximum and (maximum -1) date data (not date -1, ie max date and the 2nd max date) from the sql server.

Assume that this is my table structure and data.

S.No field1 field2 field3
---- ------ ------ ------
1 a x 2007-01-25 09:13:30.000
2 a y 2007-01-25 09:42:30.000
3 a z 2007-01-25 11:13:30.000
4 a x1 2007-01-17 09:13:30.000
5 a x2 2006-04-05 09:13:30.000
6 a x3 2007-01-17 10:23:40.000

now i want to get the maximum date and 2nd maximum date.

ie if i query the maximum date the folowing records should display.

1 a x 2007-01-25 09:13:30.000
2 a y 2007-01-25 09:42:30.000
3 a z 2007-01-25 11:13:30.000

ie if i query the 2nd maximum date, then the folowing records should display.

4 a x1 2007-01-17 09:13:30.000
6 a x3 2007-01-17 10:23:40.000

ie it should check only the dates (not date and time).it should omit the time of the date field.

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 05:12:34

SELECT TOP 2 Field3 FROM Table1 ORDER BY Field3

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-03 : 05:35:54

or

Select * from table
where dateadd(day,datediff(day,0,field3),0) =
Select min(field3) from (select top N dateadd(day,datediff(day,0,field3),0) as field3 from table order by field3 DESC)

Replace N by then number you want

Madhivanan

Failing to plan is Planning to fail

annamalai
Starting Member

5 Posts

Posted - 2007-04-03 : 06:20:28

Both the queries are not working.

The first one is not checking date only. it also checks the time also. i want to omit the time.

The second query has some errors, i think.

Select * from table
where dateadd(day,datediff(day,0,field3),0) =
Select min(field3) from (select top N dateadd(day,datediff(day,0,field3),0) as field3 from table order by field3 DESC)

in the row row of the above query, there are two select statements with from keyword, but only one matching table name.

Thanks in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-03 : 06:24:14

I missed Table name
Try this

Select * from table
where dateadd(day,datediff(day,0,field3),0) =
Select min(field3) from (select top N dateadd(day,datediff(day,0,field3),0) as field3 from table order by field3 DESC) T

Madhivanan

Failing to plan is Planning to fail

annamalai
Starting Member

5 Posts

Posted - 2007-04-03 : 07:12:02

Hai,
Thanks for replying.
This is my query, as per your reply.

Select * from cot_tab_config_details where dateadd(day,datediff(day,0,audtdate),0) =
(Select min(audtdate) from (select top 2 dateadd(day,datediff(day,0,audtdate),0) as adate from cot_tab_config_details order by audtdate DESC)cot_tab_config_details)

but it returns error
The error msg was below.

Server: Msg 147, Level 16, State 2, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Can you give me the correct one.

Thanks in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-03 : 07:18:34

Try this

Select * from cot_tab_config_details where dateadd(day,datediff(day,0,audtdate),0) =
Select min(audtdate) from (select top 2 dateadd(day,datediff(day,0,audtdate),0) as audtdate from cot_tab_config_details order by audtdate DESC) T

Madhivanan

Failing to plan is Planning to fail

annamalai
Starting Member

5 Posts

Posted - 2007-04-03 : 08:45:25

Hai,

still iam getting the error.

i was execute the following query

Select * from cot_tab_config_details where dateadd(day,datediff(day,0,audtdate),0) =
Select min(audtdate) from (select top 2 dateadd(day,datediff(day,0,audtdate),0) as audtdate from cot_tab_config_details order by audtdate DESC) T

the error msg was

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Select'.

thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 09:47:14

Are you using MICROSOFT SQL SERVER 2000 or MICROSOFT SQL SERVER 2005?

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-03 : 10:18:00

I missed braces

Select * from cot_tab_config_details where dateadd(day,datediff(day,0,audtdate),0) =
(Select min(audtdate) from (select top 2 dateadd(day,datediff(day,0,audtdate),0) as audtdate from cot_tab_config_details order by audtdate DESC) T)

Madhivanan

Failing to plan is Planning to fail

annamalai
Starting Member

5 Posts

Posted - 2007-04-04 : 04:40:22

Hai peso,

iam using SQL Server 2000.

Thanks in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-04 : 04:45:28

quote:
Originally posted by annamalai

Hai peso,

iam using SQL Server 2000.

Thanks in advance

Did you try my query?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources