The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SQL Select problem - next & previous records

Hi,

I'm trying to figure out a simple way to get a known record from a database but also get the previous and next records too. For example if I have a table:

id  name
17  rec1
42  rec2
9    rec3
77  rec4
65  rec5

and I select:

SELECT * FROM table WHERE id=9

I want to easily get id 42 & 77 as well.  Normally I'd just grab the recordset into an array and get the records that way but is there a way to do it all in one go, without hitting the database multiple times?

cheers
surreal
Monday, September 11, 2006
 
 
You could load the entire table into a recordset and then use a DataView to filter it to the one that you want.  This way you avoid multiple trips.
SteveM Send private email
Monday, September 11, 2006
 
 
if you are using oracle. Look into analytic functions. Look up 'lag' and 'lead'. Its part of the ANSI 99 standard. SQL Server 2005 may have it also.

The syntax is somewhat different from regular SQL, but its not that bad.
Ryan
Monday, September 11, 2006
 
 
This is SQL Server 2000.  Basically I let the user select a record on one page but later I want to display that record and have a link to the previous and next.  I'd hoped there might have been a simple way to do it.

Thanks for the suggestions so far.
surreal
Monday, September 11, 2006
 
 
Easy with SQL2005 using the row_number() function.

In sql 2000 you could do it by selecting an extra column which is a calculated sequence number. Use a recordset as mentioned by a previous poster, then get from the recordset where seqNo = n-1 or n+1 for previous and next.
redeye Send private email
Monday, September 11, 2006
 
 
I'm not sure if this will be quite right, but give this a read and see if it makes sense:

Your main page can remain "select where id=9".  Just calc the links on click rather than when the main page is rendered.  This does two things for you: first, it postpones calulating the next and prev id's until they're needed (so you don't need to load them if 'next' and 'prev' don't get clicked), and second, figuring the next and prev id's gets easier when done in a separate query:

select top 1 from ... where id < 9 order by id asc

and

select top 1 from ... where id > 9 order by id desc

If you really need to put these all together, I'd look at a stored proc that does this to put all recs into one result set.
D. Lambert Send private email
Monday, September 11, 2006
 
 
don't know if this works in SQL Server, but:

SELECT * FROM table WHERE id in (9,27,47)
Steve Hirsch Send private email
Monday, September 11, 2006
 
 
try

select *
from table
where id = 475
    or id = ( select max(id) from table where id < 475)
    or id = ( select min(id) from table where id > 475)

this assumes the "id" column defines the order of the records.  if it doesn't, then the min/max query will have to be changed to fit whatever defines the prev/next item.
Another Anonymous Coward
Monday, September 11, 2006
 
 
In my case, sometimes I order by date, and they be equal, so it's a little trickier I think. But the solutions already provided above should suffice as a starting point, at least.
Lostacular
Monday, September 11, 2006
 
 
In this case, I would almost always use a client-side solution - your data would appear to be sorting on name, and what happens if there are duplicates - what determines the ordering?

However, the following SQL returns the entire rowset with pointers to next and previous:

SELECT cur.[id], cur.[name],
    previdlookup.[id] as previd, prevnamelookup.[name] as prevname,
    nextidlookup.[id] as nextid, nextnamelookup.[name] as nextname
FROM Table1 cur
LEFT JOIN
(
    SELECT cur.[name], MAX(prev.[name])
    FROM Table1 cur
    INNER JOIN Table1 prev
        ON cur.[name] > prev.[name]
    GROUP BY cur.[name]
) AS prevnamelookup (curname, [name])
    ON cur.[name] = prevnamelookup.curname
LEFT JOIN Table1 previdlookup
    ON previdlookup.[name] = prevnamelookup.[name]
LEFT JOIN
(
    SELECT cur.[name], MIN([next].[name])
    FROM Table1 cur
    INNER JOIN Table1 [next]
        ON cur.[name] < [next].[name]
    GROUP BY cur.[name]
) AS nextnamelookup (curname, [name])
    ON cur.[name] = nextnamelookup.curname
LEFT JOIN Table1 nextidlookup
    ON nextidlookup.[name] = nextnamelookup.[name]
ORDER BY cur.[name]

If a server-side scenario is required, I would probably just have an SP return a single row and have it lookup the next and previous records and add those columns.  However, the same problems arise, because you can't get two values from the single row determined to be the MAX (whether it's by ID or by name).
Cade Roux Send private email
Monday, September 11, 2006
 
 
fantastic, thanks all!
surreal
Monday, September 11, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz