CODEWITHSUNDEEP
sqlsql-server-2008-r2
Mike
Mike

Reputation: 2434

Select previous row for each entry in Table/List

Row       TimeStamp
____|________________________
1   | 2015-01-01 12:00:01.000
2   | 2015-01-01 12:00:02.000
3   | 2015-01-01 12:00:03.000
4   | 2015-01-01 12:00:04.000
5   | 2015-01-01 12:00:05.000
6   | 2015-01-01 12:00:06.000
7   | 2015-01-01 12:00:07.000
8   | 2015-01-01 12:00:08.000
9   | 2015-01-01 12:00:09.000

Selecting the previous row's TimeStamp has been a rather simple task

e.g.

SELECT  MAX([TimeStamp])
FROM [MyTable] 
WHERE [TimeStamp] < '2015-01-01 12:00:02.000'

gets 2015-01-01 12:00:01.000 as expected.

However I'm having some trouble selecting a list of TimeStamps from multiple preceding rows.

For example, if I wanted to get the timestamps of the preceding rows for Row >= 3 && <= 6 

(i.e. 
    SELECT [TimeStamp]
    FROM [MyTable] 
    WHERE [Row] >= 3 AND [Row] <= 6

    =>

    TimeStamps     
    2015-01-01 12:00:03.000
    2015-01-01 12:00:04.000
    2015-01-01 12:00:05.000
    2015-01-01 12:00:06.000
)

How would I go about getting the preceding TimeStamp for each of these result rows?

(i.e. 
    TimeStamps                   
    2015-01-01 12:00:02.000
    2015-01-01 12:00:03.000
    2015-01-01 12:00:04.000
    2015-01-01 12:00:05.000
)

I've seen quite a few solutions related to lag/lead, but my usage of SQLServer 2008 is difficult to change.

Upvotes: 1

Views: 74

Answers (3)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

select b.id, max(a.timestamp)
from mytable a join mytable b 
on a.id < b.id
where b.id between --desired values
group by b.id

Here is a fiddle with sample data: fiddle

will this work for you? This will join on all the previous rows and then picks up the max timestamp which would always be the value of the previous row, assuming timestamp column is ordered ascending.

Upvotes: 0

morgb
morgb

Reputation: 2312

You could try this:

SELECT 
    t.TimeStamp,
    (
     SELECT MAX(t1.TimeStamp)
        FROM MyTable t1
        WHERE t1.Row < t.Row
    ) AS PrevTimeStamp
FROM MyTable t
WHERE t.Row >= 3 AND t.Row <= 6

This would give you side-by-side columns, one current and one previous.

Upvotes: 1

Andomar
Andomar

Reputation: 238048

You can use a CTE to number rows, and look up the previous one with a left join:

; with  numbered as
        (
        select  row_number() over (order by TimeStamp) as rn
        ,       *
        from    YourTable
        )
select  cur.TimeStamp
,       prev.TimeStamp
from    numbered cur
left join
        numbered prev
on      prev.rn + 1 = cur.rn
where   cur.row between 3 and 6

Upvotes: 0

Related Questions