CODEWITHSUNDEEP
sqlsql-server-2005
kheya
kheya

Reputation: 7612

Help me with my select query

I have this table in sql server 2005:

id   student active
1   Bob     1
3   Rob     0
5   Steve   1
7   John    1
8   Mark    0
10   Dave    0
16   Nick    1

My select query returns an active student by a given id. But I also want to return the ids of prev and next student who are active. If no prev, it will be 0 or null. Same for next.

Example: for id=5, my select would return

id    student    prev_id    next_id
5     steve       1          7

Example: for id=7, my select would return

id    student    prev_id    next_id
7     John       5          16

Example: for id=16, my select would return

id    student    prev_id    next_id
16    Nick       7          0

How do I write this select query?

I have query but I just can't get the prev id correctly. It always returns the first active id.

Thanks

EDIT: Here is the query I have right now.

select id, student, 
(select top 1 id from test where id<7 and active=1) as prev,
(select top 1 id from test where id>7 and active=1) as next 
from test where id=7--I used 7 just as an example. it will be a parameter

Upvotes: 1

Views: 121

Answers (5)

Phil Helmer
Phil Helmer

Reputation: 1240

This will give you a little more control, especially since you are paginating.

WITH NumberedSet AS (
    SELECT  s.id, 
            s.student,
            row_number() OVER (ORDER BY s.id) AS rownum
    FROM    dbo.students AS s
    WHERE   s.active = 1
)
SELECT  cur.id, 
        cur.student, 
        isnull(prv.id,0) AS prev_id, 
        isnull(nxt.id,0) AS next_id
FROM    NumberedSet AS cur
        LEFT JOIN NumberedSet AS prv ON cur.rownum - 1 = prv.rownum
        LEFT JOIN NumberedSet AS nxt ON cur.rownum + 1 = nxt.rownum     
;

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

Working sample

DECLARE @T TABLE (id int, student varchar(10), active bit)
insert @t select
1 ,'Bob', 1 union all select
3 ,'Rob', 0 union all select
5 ,'Steve', 1 union all select
7 ,'John', 1 union all select
8 ,'Mark', 0 union all select
10 ,'Dave', 0 union all select
16 ,'Nick', 1

---- your query starts below this line

declare @id int set @id = 5

select id, student,
  isnull((select top(1) Prev.id from @T Prev
   where Prev.id < T.id and Prev.active=1
   order by Prev.id desc),0) Prev,
  isnull((select top(1) Next.id from @T Next
   where Next.id > T.id and Next.active=1
   order by Next.id),0) Next
from @T T
where id = @id

The ISNULLs are to return 0 when there is no match - NULL would have worked fine but your question has 0 when there is no Next.

Upvotes: 2

JohnP
JohnP

Reputation: 50019

You could use a nested query. I obviously can't test this out, but you should get the idea.

SELECT id, student ,
(SELECT C1.id FROM students S1 WHERE S1.active = 1 AND S1.id < S.id LIMIT 1) AS beforeActive, 
(SELECT C2.id FROM categories S2 WHERE S2.active = 1 AND S2.id > S.id LIMIT 1)  AS afterActive
FROM students S

Efficiency wise, I've no idea how well this query will perform

Upvotes: 1

Iain
Iain

Reputation: 6452

You may want to take a look at Common Table Expression, a feature for only SQL Server for recursive queries, you can find a link here

But this sound like homework, and probebly not the right forum to ask it in.

Regards

Upvotes: 1

John Petrak
John Petrak

Reputation: 2928

try something like this

SELECT  ID,
        Student,
        ( SELECT TOP 1
                    ID
          FROM      dbo.table AS pT
          WHERE     pT.ID < T.ID And Active = 1
          ORDER BY  ID DESC
        ) AS PrevID,
        ( SELECT TOP 1
                    ID
          FROM      dbo.table AS pT
          WHERE     pT.ID > T.ID And Active = 1
          ORDER BY  ID
        ) AS NextID
FROM    dbo.table AS T

Upvotes: 5

Related Questions