Selecting the next record based on an ORDER BY clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Selecting the next record based on an ORDER BY clause

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-12-27 : 11:44:08

John writes "I want to be able to select the next record in a table based on a specified sort order and matching criteria of the current record.

For example, consider the following table extract;

ID Surname FirstName
7 Jones Adam
9 Jones John
2 Jones Mark
10 Smith Bob

Now assume that I am currently working with data from item 9 (i.e. John Jones) What I want to do is have a simple SQL statement that will return the next record in the table. I want the query to return just one record, i.e. I don't want to have to return the entire table and simply perform a move next.

In the above example, I am using an order by clause of Surname, FirstName, ID. I then want to be able to change this order by clause to, say, FirstName, Surname, ID and perform the same type of query, i.e. return the record after the one that matches my current criteria based on the order defined by my ORDER BY clause.

I am using SQL Server 2000."

verronep
Starting Member

15 Posts

Posted - 2002-12-27 : 12:08:04

I'm not sure I like my own solution, but it's the only one I can think of at the moment...

DECLARE @T TABLE
(
ukey int IDENTITY(1,1),
[id] int,
Surname varchar(50),
FirstName varchar(50)
)

-- Do an insert into the temp table according to your query order
-- criteria.
-- (Ex.) INSERT INTO @T SELECT * FROM Table ORDER BY etc...
INSERT INTO @T VALUES(2, 'Jones', 'Mark')
INSERT INTO @T VALUES(7, 'Jones', 'Adam')
INSERT INTO @T VALUES(9, 'Jones', 'John')
INSERT INTO @T VALUES(10, 'Smith', 'Bob')

-- Get the id of the currently displayed record
DECLARE @CurID int
SET @CurID = 7

-- Select the record with the next identity in the list
IF @CurID IS NULL SELECT TOP 1 * FROM @T
ELSE
BEGIN
SELECT * FROM @T
WHERE ukey = (SELECT ukey FROM @T WHERE [id] = @CurID) + 1
END

HTH
Paul

"I have not failed. I have just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

Edited by - verronep on 12/27/2002 12:12:53

Edited by - verronep on 12/27/2002 12:17:40

verronep
Starting Member

15 Posts

Posted - 2002-12-27 : 12:46:12

Is there any particular reason you're trying to get only one record at a time returned? It's just not going to scale well at all, especially if the number of rows in the table is of a decent size

How many records are you expecting to return? How dynamic is the data you return?

While I suppose my solution would work, I really think this is something better done on the front end.

Just my two cents.

"I have not failed. I have just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-27 : 14:13:28

If you want to sort by Surname and then firstname, and @CurrID is the current record you are on, the following should do the trick. You can change the sort as needed in different stored procs. I am not sure what you mean by altering the sort -- do you need only two possibilities, or does it have to be very dynamic?

Anyway, try it out and see how it works. A big key to the ordering and getting the right records (in addition to the ORDER BY clause) is the JOIN clause.



declare @table table ([ID] int, Surname varchar(50), firstname varchar(50));
declare @CurrID int;

insert into @table values (7,'Jones','Adam');
insert into @table values (9,'Jones','John');
insert into @table values (2,'Jones','Mark');
insert into @table values (10,'Smith','Bob');
insert into @table values (12,'Smith','Jim');

set @CurrID = 2;  /* this is the current ID you are 'on' */

SELECT * FROM
(
SELECT TOP 2 A.*
FROM
@table A
INNER JOIN
(SELECT * FROM @table WHERE ID = @CurrID) B
ON
((A.Surname = B.Surname) AND (A.Firstname >= B.FirstName)) OR
(A.Surname > B.Surname)
ORDER BY A.Surname, A.FirstName, A.ID
) TwoRecords
WHERE TwoRecords.ID <> @CurrID

- Jeff

skillile
Posting Yak Master

208 Posts

Posted - 2002-12-28 : 08:16:55

You might have to modify to search for surname but I think this should do it.

--NOTE: When you hit the last row this proc will
-- start from the beginning. If you don't want this
-- just check for the last row first
-- you also might need to put >= on the compare

DECLARE @cur_rec int
SET @cur_rec = 9

DECLARE @temp TABLE ( rowint int, fname varchar(10), lname varchar(10) )
INSERT INTO @temp VALUES(7, 'Jones', 'Adam' )
INSERT INTO @temp VALUES(9, 'Jones', 'John' )
INSERT INTO @temp VALUES(2, 'Jones', 'Mark' )
INSERT INTO @temp VALUES(10, 'Smith', 'Bob' )

SET ROWCOUNT 1
SELECT *
FROM @temp
ORDER BY
CASE WHEN rowint > @cur_rec THEN 0 ELSE 1 END, rowint

slow down to move faster...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-30 : 13:21:53

skillile --

I am not sure your technique works, you need to order by name and not by ID. If you order by ID it is really easy.

For example, your script returns Bob Smith, when what should be returned is Mark Jones.

Also, here is an improved (shorter) version of my SELECT statement:

SELECT TOP 1 A.*
FROM
@table A
INNER JOIN
(SELECT * FROM @table WHERE ID = @CurrID) B
ON
((A.Surname = B.Surname) AND (A.Firstname >= B.FirstName)) OR
(A.Surname > B.Surname)
WHERE A.ID <> @CurrID
ORDER BY A.Surname, A.FirstName, A.ID

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources