Returning the previous rows date? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Returning the previous rows date?

Author

Topic

sarblota
Starting Member

8 Posts

Posted - 2008-10-16 : 04:03:53

Hi

Have a table as follows (truncated for simplicity):

Key date
P1 1/1/06
P1 15/2/07
P1 13/2/08
P2 1/1/05

What I want to do is to return each P1 with the previous highest date.

So the return would look like.

Key Date LastDate
P1 13/2/08 15/2/07
P1 15/2/07 1/1/06
P1 1/1/06 NULL
P2 1/1/05 NULL

and so on.

Trying all sorts to no avail!

cheers

Sarb Lota

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 04:09:43

[code]SELECT t.Key,t.date,prev.date
FROM Table t
OUTER APPLY (SELECT TOP 1 date
FROM Table
WHERE Key=t.Key
AND date <t.date
ORDER BY date DESC)prev[/code]

sarblota
Starting Member

8 Posts

Posted - 2008-10-16 : 04:14:38

Sorry all!

Just remembered that I'm using sql server 2000!!!

cheers

Sarb Lota

sarblota
Starting Member

8 Posts

Posted - 2008-10-16 : 04:23:44

HI Visakh

Thanks for the prompt reponse.

I've tried your code snippet with no luck. Is this 2005 specific SQL?

cheers

Sarb Lota

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 04:25:20

quote:
Originally posted by sarblota

Sorry all!

Just remembered that I'm using sql server 2000!!!

cheers

Sarb Lota

then remember to post in correct forums in future. try the below

SELECT t1.key,t1.date,MAX(t2.date) AS prevdate
FROM table t1
INNER JOIN table t2
ON t2.Key=t1.Key
AND t2.date<t1.date
GROUP BY t1.key,t1.date

sarblota
Starting Member

8 Posts

Posted - 2008-10-16 : 04:35:04

Thanks alot.

First post so apologies for posting to the wrong forum!

cheers

Sarb Lota

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 04:48:07

quote:
Originally posted by sarblota

Thanks alot.

First post so apologies for posting to the wrong forum!

cheers

Sarb Lota

No worries. You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources