CODEWITHSUNDEEP
sqlsql-servert-sql
tmck8
tmck8

Reputation: 3

Find next row with specific value in a given row

The table I have now looks something like this. Each row has a time value (on which the table is sorted in ascending order), and two values which can be replicated across rows:

Key      TimeCall     R_ID      S_ID
-------------------------------------------
1          100          40        A
2          101          50        B
3          102          40        C
4          103          50        D
5          104          60        A
6          105          40        B

I would like to return something like this, wherein for each row, a JOIN is applied such that the S_ID and Time_Call of the next row that shares that row's R_ID is displayed (or is NULL if that row is the last instance of a given R_ID). Example:

Key        TimeCall     R_ID      S_ID      NextTimeCall      NextS_ID
----------------------------------------------------------------------
1          100          40        A         102               C
2          101          50        B         103               D
3          102          40        C         105               B
4          103          50        D         NULL              NULL
5          104          60        A         NULL              NULL
6          105          40        B         NULL              NULL

Any advice on how to do this would be much appreciated. Right now I'm joining the table on itself and staggering the key on which I'm joining, but I know this won't work for the instance that I've outlined above:

SELECT TOP 10 Table.*, Table2.TimeCall AS NextTimeCall, Table2.S_ID AS NextS_ID
    FROM tempdb..#Table AS Table
        INNER JOIN tempdb..#Table AS Table2
        ON Table.TimeCall + 1 = Table2.TimeCall

So if anyone could show me how to do this such that it can call rows that aren't just consecutive, much obliged!

Upvotes: 0

Views: 118

Answers (4)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Use LEAD() function:

SELECT * 
, LEAD(TimeCall) OVER (PARTITiON BY R_ID ORDER BY [Key]) AS NextTimeCall
, LEAD(S_ID) OVER (PARTITiON BY R_ID ORDER BY [Key]) AS NextS_ID
FROM Table2
ORDER BY [Key]

SQLFiddle DEMO

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

For older versions, here is one trick using Outer Apply 

SELECT a.*, 
       nexttimecall, 
       nexts_id 
FROM   table1 a 
       OUTER apply (SELECT TOP 1 timecall,s_id 
                    FROM   table1 b 
                    WHERE  a.r_id = b.r_id 
                           AND a.[key] < b.[key] 
                    ORDER  BY [key] ASC) oa (nexttimecall, nexts_id)

Note : It is better to avoid reserved keywords(Key) as column/table names.

Upvotes: 0

wrslphil
wrslphil

Reputation: 258

Use an OUTER APPLY to select the top 1 value that has the same R_ID as the first Query and has a higher Key field

Just change the TableName to the actual name of your table in both parts of the query

SELECT a.*, b.TimeCall as NextTimeCall, b.S_ID as NextS_ID FROM
    (
    SELECT * FROM TableName as a
    ) as a
    OUTER APPLY 
    (
    SELECT TOP 1 FROM TableName as b
    WHERE a.R_ID = b.R_ID
    AND a.Key > B.Key
    ORDER BY Key ASC
    ) as b

Hope this helps! :)

Upvotes: 0

Veljko89
Veljko89

Reputation: 1953

This is only test example I had close by ... but i think it could help you out, just adapt it to your case, it uses Lag and Lead ... and it's for SQL Server

if object_id('tempdb..#Test') IS NOT NULL drop table #Test
create table #Test (id int, value int)

insert into #Test (id, value)
values
(1, 1),
(1, 2), 
(1, 3)

select id, 
    value, 
    lag(value, 1, 0) over (order by id) as [PreviusValue], 
    lead(Value, 1, 0) over (order by id) as [NextValue] 
from #Test

Results are 

id  value   PreviusValue    NextValue
1     1         0               2   
1     2         1               3
1     3         2               0

Upvotes: 0

Related Questions