max(mydate) -1? - 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

SQL Server Development (2000)

max(mydate) -1?

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-04-03 : 11:46:34

Hi, my sql gurus.

I need to select (or in the where clause) a date that is directly before my max(datefield).

Using Northwind as an example,

select max(orderdate) from orders
--gave me 1998-05-06

select max(orderdate)-1 from orders
--gave me 1998-05-05

max(orderdate)-1 wouldn't work because it gave me that date regardless that is the date on my table or not.

In another words, I want to get to my next max orderdate as if my current max orderdate is not there.
i.e. if my data looks like this,
orderdate
1998-05-06
1998-05-03
1998-05-01
...
I want to return 05-03.

Thanks!

Kristen
Test

22859 Posts

Posted - 2006-04-03 : 11:48:45

Something like this?


select max(orderdate)
from orders
WHERE orderdate < 
(
    select max(orderdate)
    from orders
)

Kristen

Kristen
Test

22859 Posts

Posted - 2006-04-03 : 11:50:27

Dunno if that's less efficient than:


SELECT TOP 1 orderdate
FROM
(
    SELECT TOP 2 orderdate
    FROM orders
    ORDER BY orderdate DESC
) X
ORDER BY orderdate ASC

However, this solution will give you the MAX date if there are duplicate values, the previous one won't ...

Edit: You could chuck a DISTINCT in the "SELECT TOP 2 ..." phrase to work around that if you like ... but that may wreck performance compared to the MAX(...) solution above

Kristen

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-04-03 : 11:55:32

Cool!
Thanks, Kristen!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-04 : 02:27:55

Other method

Select min(orderdate) from
(
SELECT TOP 2 orderdate
FROM orders
ORDER BY orderdate DESC
) T

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources