Selecting the record before and after

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 record before and after

Author

Topic

jmcbride
Starting Member

24 Posts

Posted - 2003-04-23 : 12:25:16

I have an Record with a unique id number. Through one query, I would like to select the record directly before and after it. The id numbers are sequential but contain many holes as records have been deleted.

Thanks!

--------------------
http://www.utsa.edu/
(Joe) Joseph McBride

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-23 : 12:45:04

USE Northwind
GO
CREATE TABLE Table2 (col1 int, col2 int)
GO
INSERT INTO Table2 (col1,col2) SELECT 1,2 Union All SELECT 2,3 Union All SELECT 3,5 Union All SELECT 5,6
GO

SELECT * FROM Table2 Where col1 IN (SELECT Max(Col1) FROM Table2 WHERE Col1 < 3)
UNION ALL
SELECT * FROM Table2 Where col1=3
UNION ALL
SELECT * FROM Table2 Where col1 IN (SELECT MIN(Col1) FROM Table2 WHERE Col1 > 3)
GO

DROP TABLE Table2
GO

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-23 : 12:45:27

There are a couple of ways, here's one:
DECLARE @CurrentID INT

-- Define the Current ID somehow

SELECT TOP 1 *

FROM MyTable

WHERE ID < @CurrentID -- Get IDs before @CurrentID

ORDER BY ID DESC -- Order 'em

for the ID following @CurrentID try the same thing only different

Sam

dsdeming

479 Posts

Posted - 2003-04-23 : 12:52:15

To get them both in one statement:

SELECT TOP 2 *
FROM Table
Where Column >= ( SELECT Max( Column ) FROM Table WHERE Column < YourValue ) AND Column <> YourValue

HTH

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-23 : 13:02:24

oooooooohhhhh...

I like that one.

VERY Clever.

Brett

8-)

jmcbride
Starting Member

24 Posts

Posted - 2003-04-23 : 13:46:37

Will all of these get the records before AND after the record I specify?

--------------------
http://www.utsa.edu/
(Joe) Joseph McBride

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-23 : 13:50:30

Joe:

I have a suggestion: Try them out and see which does what you want! If none work quite right, or you have trouble implementing them, let us know.

dsdeming:

Very nice! I posted something similar to that a while ago .. can't remember what the topic was. But a very clever use of TOP.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-23 : 14:01:16

I didn't think I could make it easier...even gave the DDL/DML.

Just cut and paste it in to QA.

And I tired dsdeming solution (did I say VERY clever?):

You can cut and paste this too. For further testing just add mor rows with the INSERT I gave you:

SELECT TOP 2 *
FROM Table2
Where Col1 >= ( SELECT Max( Col1 ) FROM Table2 WHERE Col1 < 3 ) AND Col1 <> 3

PS If you only want the two rows, then remove my middle SELECT.

Brett

8-)

dsdeming

479 Posts

Posted - 2003-04-23 : 14:49:31

OK, stop already. You made me blush.

jmcbride
Starting Member

24 Posts

Posted - 2003-04-23 : 15:00:30

Thanks, the query is working great. Now I'm haveing an ordering problem. I would like for the record that I specified to appear before the other two records. I have been using this:

(SELECT TOP 2 *
FROM photogallery
Where (photoID >= ( SELECT Max( PhotoID ) FROM PhotoGallery WHERE PhotoID < 10 )
AND (PhotoID <> 10))
)
UNION
Select * from photogallery where photoID=10

But it does not always order the the two outer records properly. I tried throwing in order by but have been receiving errors.

--------------------
http://www.utsa.edu/
(Joe) Joseph McBride

jmcbride
Starting Member

24 Posts

Posted - 2003-04-23 : 15:06:38

Thanks, sorry about all of the questions, I should have looked in detail at some of the other queries.

Problem solved.

--------------------
http://www.utsa.edu/
(Joe) Joseph McBride

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-23 : 15:08:12

select * from
(
SELECT TOP 3 *
FROM photogallery
Where (photoID >= ( SELECT Max( PhotoID ) FROM PhotoGallery WHERE PhotoID < 10)
order by photoID
) as a
order by case when photoID = 10 then 1 else 2 end, photoID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources