get max date and date prior to max date

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

General SQL Server Forums

New to SQL Server Programming

get max date and date prior to max date

Author

Topic

TPie9
Yak Posting Veteran

67 Posts

Posted - 2011-01-21 : 09:33:09

I have a customer table that gives me the customer name and dates of their shipments with us. How do I get the last date they shipped with us and the date previous to that? I can get the last date they shipped with us by using the max date function, but don't know how to get the ship date prior to that (last date shipped). I tried using a sub query where the date not exists, but that didn't seem to work.

Any ideas?

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-21 : 09:37:55

select custName,shipDate from
(
select
row_number() over (partition by custId order by shipDate desc) as rownum,
custName,
shipDate
from table
)dt
where rownum < 3

No, you're never too old to Yak'n'Roll if you're too young to die.