Solution for recordset get next and get last rows

[JimFL]

Does anybody have a good solution for this?

I have a table below

select * from table

ID, TITLE
1 a
2 b
3 c
4 d


I need to query the table at the ID each time but return
the ID above and below the selected row.

So if I query the row at

select * from table where ID=2
I want ID=1 as the last and ID=3 as the next

also

select * from table where ID=1
I want ID=4 as the last and ID=2 as the next

So that the results from my asp page roll around. Is there a simple solution to ?

 

[vbkris]

why not try this in ur ASP page:

sql="select * from table where ID in (" & CurrentId-1 & "," & CurrentId & "," & CurrentId+1 & ")"

that must give u the answer.

if however you want it on SQL Server side then u may have to use SPs to do this. dont think this is possible in a single SQL...

Known is handfull, Unknown is worldfull

 

[vbkris]

try this out too:

SET ROWCOUNT 3;select [Id] from Table1 where [Id]<=(CURRENT_ID+1) order by [Id] desc;SET ROWCOUNT 0;

Known is handfull, Unknown is worldfull

 

[JimFL]

Thanks,

Ive opted for a similar format to the above in that I have used sql to do the hard work.


SELECT s.ID,
(select max(ID) from x where x.ID < s.ID)as lastS,
(select min(ID) from x where x.ID > s.ID)as nextS,
(select min(ID) from x where x.ID = s.ID)as minS,
(select max(ID) from x where x.ID = s.ID)as maxS
FROM s
WHERE (s.ID = ??)


Then if the lastS or nextS is null I use the minS or maxS values.

Not the prettiest but a valid solution netherless.

Thanks again for your help.. always useful to know other ways of looking into problems

 

[vbkris]

welcome...

Known is handfull, Unknown is worldfull