How to populate the pervious record? - 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)

How to populate the pervious record?

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2009-10-23 : 06:12:36

Hello All,

The below code suppose to find the previous record date, but not working properly because the current record date is not unique therefore the col3 is showing as prev_col3, think…

with CTE
as
(
select A.COL1, A.COL2, A.COL3, ROW_NUMBER() OVER (ORDER BY COL3 ASC) AS row_no
from TEST_TABLE A inner join DPULL B on A.CPK = B.DPK
inner join DCELL C on C.DCK = A.DCK
)
select p.COL3 as [prev_COL3], c.*
into Table_1
from CTE c
left join CTE p on c.row_no = p.row_no + 1;
Does anyone know what I’m doing wrong?

Below are the sample table to illustrate my concerns.

BEFORE
PREV_COL3 COL1 COL2 COL3

8/31/09 12:00 AM 123 2009 8/31/09 12:00 AM
8/31/09 12:00 AM 123 2009 8/31/09 12:00 AM
8/31/09 12:00 AM 123 2009 8/31/09 12:00 AM

AFTER
PREV_COL3 COL1 COL2 COL3

7/15/09 12:00 AM 123 2009 8/31/09 12:00 AM
7/15/09 12:00 AM 123 2009 8/31/09 12:00 AM
7/15/09 12:00 AM 123 2009 8/31/09 12:00 AM

Please advice.

Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-23 : 10:31:03

Is there any way you can look at a series of rows that have the same COL3 date and tell what the order should be? If so, include those columns (or expressions) in the OVER (ORDER BY...) clause. If not then there's no possible solution :)

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 13:12:30

quote:
Originally posted by pras2007

Hello All,

The below code suppose to find the previous record date, but not working properly because the current record date is not unique therefore the col3 is showing as prev_col3, think…

with CTE
as
(
select A.COL1, A.COL2, A.COL3, DENSE_RANK()~~ROW_NUMBER()~~ OVER (ORDER BY COL3 ASC) AS row_no
from TEST_TABLE A inner join DPULL B on A.CPK = B.DPK
inner join DCELL C on C.DCK = A.DCK
)
select p.COL3 as [prev_COL3], c.*
into Table_1
from CTE c
left join CTE p on c.row_no = p.row_no + 1;
Does anyone know what I’m doing wrong?

Below are the sample table to illustrate my concerns.

BEFORE
PREV_COL3 COL1 COL2 COL3

8/31/09 12:00 AM 123 2009 8/31/09 12:00 AM
8/31/09 12:00 AM 123 2009 8/31/09 12:00 AM
8/31/09 12:00 AM 123 2009 8/31/09 12:00 AM

AFTER
PREV_COL3 COL1 COL2 COL3

7/15/09 12:00 AM 123 2009 8/31/09 12:00 AM
7/15/09 12:00 AM 123 2009 8/31/09 12:00 AM
7/15/09 12:00 AM 123 2009 8/31/09 12:00 AM

Please advice.

Thanks.

modify like above and see

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources