Previous and next ID

I'd like to find the previous and next record of a table based on the numeri
c
value of an identity column. For example, consider the following sample dat
a:
RecID | theValue
--
1 | first
2 | second
4 | third
6 | fourth
7 | fifth
If the value '4' is passed in to my query (via ASP.NET app), I can get the
previous/next records by running these two queries:
SELECT TOP 1 RecID
FROM theTable
WHERE RecID < 4
ORDER BY RecID DESC
SELECT TOP 1 RecID
FROM theTable
WHERE RecID > 4
ORDER BY RecID ASC
I'm trying to combine these two queries into a single query to return those
two values, but I'm banging my head against the wall. If someone could give
me insight or a completely different path to follow, I'd appreciate it.
ThanksTry:
SELECT *
FROM
(
SELECT TOP 1 RecID
FROM theTable
WHERE RecID < 4
ORDER BY RecID DESC
) x
UNION ALL
SELECT TOP 1 RecID
FROM theTable
WHERE RecID > 4
ORDER BY RecID ASC
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:157D705F-C3F6-4E7A-AE2F-AA675E4BD31B@.microsoft.com...
I'd like to find the previous and next record of a table based on the
numeric
value of an identity column. For example, consider the following sample
data:
RecID | theValue
--
1 | first
2 | second
4 | third
6 | fourth
7 | fifth
If the value '4' is passed in to my query (via ASP.NET app), I can get the
previous/next records by running these two queries:
SELECT TOP 1 RecID
FROM theTable
WHERE RecID < 4
ORDER BY RecID DESC
SELECT TOP 1 RecID
FROM theTable
WHERE RecID > 4
ORDER BY RecID ASC
I'm trying to combine these two queries into a single query to return those
two values, but I'm banging my head against the wall. If someone could give
me insight or a completely different path to follow, I'd appreciate it.
Thanks|||Thanks for the quick reply, Tom.
But it seems to be ignoring the ORDER BY clause in the second query.
The result is:
RecID
--
2
7
The 2 is correct, but the 7 is not. Any ideas?
"Tom Moreau" wrote:

> Try:
> SELECT *
> FROM
> (
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID < 4
> ORDER BY RecID DESC
> ) x
> UNION ALL
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID > 4
> ORDER BY RecID ASC
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:157D705F-C3F6-4E7A-AE2F-AA675E4BD31B@.microsoft.com...
> I'd like to find the previous and next record of a table based on the
> numeric
> value of an identity column. For example, consider the following sample
> data:
> RecID | theValue
> --
> 1 | first
> 2 | second
> 4 | third
> 6 | fourth
> 7 | fifth
> If the value '4' is passed in to my query (via ASP.NET app), I can get the
> previous/next records by running these two queries:
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID < 4
> ORDER BY RecID DESC
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID > 4
> ORDER BY RecID ASC
> I'm trying to combine these two queries into a single query to return thos
e
> two values, but I'm banging my head against the wall. If someone could gi
ve
> me insight or a completely different path to follow, I'd appreciate it.
> Thanks
>
>|||select recid
(select max(recid) from thetable t2 where t2.recid<t1.recid) prevID,
(select min(recid) from thetable t2 where t2.recid>t1.recid) nextID
from theTable t1
on SQL 2K5 use row_number()|||How about :
SELECT *
FROM
(
SELECT TOP 1 RecID
FROM theTable
WHERE RecID < 4
ORDER BY RecID DESC
) x
UNION ALL
SELECT *
FROM
(
SELECT TOP 1 RecID
FROM theTable
WHERE RecID > 4
ORDER BY RecID ASC
) y
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:FB304593-6491-434F-8315-A4E69D7BDE1D@.microsoft.com...
Thanks for the quick reply, Tom.
But it seems to be ignoring the ORDER BY clause in the second query.
The result is:
RecID
--
2
7
The 2 is correct, but the 7 is not. Any ideas?
"Tom Moreau" wrote:

> Try:
> SELECT *
> FROM
> (
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID < 4
> ORDER BY RecID DESC
> ) x
> UNION ALL
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID > 4
> ORDER BY RecID ASC
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:157D705F-C3F6-4E7A-AE2F-AA675E4BD31B@.microsoft.com...
> I'd like to find the previous and next record of a table based on the
> numeric
> value of an identity column. For example, consider the following sample
> data:
> RecID | theValue
> --
> 1 | first
> 2 | second
> 4 | third
> 6 | fourth
> 7 | fifth
> If the value '4' is passed in to my query (via ASP.NET app), I can get the
> previous/next records by running these two queries:
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID < 4
> ORDER BY RecID DESC
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID > 4
> ORDER BY RecID ASC
> I'm trying to combine these two queries into a single query to return
> those
> two values, but I'm banging my head against the wall. If someone could
> give
> me insight or a completely different path to follow, I'd appreciate it.
> Thanks
>
>|||That did it Alexander. Thanks.
"Alexander Kuznetsov" wrote:

> select recid
> (select max(recid) from thetable t2 where t2.recid<t1.recid) prevID,
> (select min(recid) from thetable t2 where t2.recid>t1.recid) nextID
> from theTable t1
> on SQL 2K5 use row_number()
>|||Do you know the differences in rows and records? Do you now that you
are mimicking a magnetic tape file in SQL? Why did you use the
proprietary SELECT TOP syntax?
SELECT MIN(F1.foo_id) AS prev_tape_position,
MAX(F2.foo_id) AS next_tape_position
FROM FakeTape AS F1, FakeTape AS F2
WHERE F1.foo_id > @.current_tape_position
AND F2.foo_id < @.current_tape_position;
I am not usre what you want to do when the imaginary read head is on
the first or last "record".