Select next item in DB without knowing what ID it has

[Neo Geshel]

I have an Access DB, from which I am going to pull images. Each image
has an associated ID, but the ID's are not necessarily sequential (some
images may have been deleted, leaving gaps in the list of ID's).

I am looking to be able to call an ID and its Image from the database,
but also have returned the previous ID and the next ID, even when those
actual ID's are not necessarily +1 and -1 to the ID being called.

For example, I want to bring up an image with the ID of 22. There *had*
been images with ID's of 21, 23 and 24, but they had been deleted in the
past. I want to be able to have returned, in the same query (or be able
to immediately use another query to discover them) the ID's of 20 and
25, because these are the ID's of currently existing photos that are
directly before and after the photo that has the ID of 22.

Is there anywhere where I can see and example that makes use of images
stored in a database?

TIA
....Geshel
--
***********************************************************************
* My reply-to is an automatically monitored spam honeypot. Do not use *
* it unless you want to be blacklisted by SpamCop. Please reply to my *
* first name at my last name dot org. *
***********************************************************************

 

[Onin Tayson]

you can use the TOP & UNION keywords

select id from table where id = @id union
select top 1 id from table where id<@id union
select top 1 id from table where id>@id

HTH,

 

[Neo Geshel]

you can use the TOP & UNION keywords

select id from table where id = @id union
select top 1 id from table where id<@id union
select top 1 id from table where id>@id

HTH,

(bottom-posted for clarity: http://www.caliburn.nl/topposting.html)

You know, I didn't think of that at all. Although I am curious, the way
I read the third line, it wouldn't bring me #25, but rather the last ID
(the highest numbered ID) in the list of images.

Although I'm not an SQL guru, shouldn't the second and third lines be
ORDER BY [ID], where the second line is normal (ASC) and the third line
is reversed (DESC)? That way, the second line has the highest number
under 22 at the top, and the third line has the lowest number over 22 at
the top.

As well, since three ID's are being returned, they'll have to be renamed
before being plunked into the repeater, no?

I see the query as being:

SELECT TOP 1 [ID] AS [prev] FROM [tblImages] WHERE [ID]<@id SORT BY [ID]
ASC UNION
SELECT TOP 1 [ID] AS [next] FROM [tblImages] WHERE [ID]>@id SORT BY [ID]
DESC

This way, the ID of the image requested is being used to pull two more
ID's from the database. One ID is the highest one beneath it, the other
is the lowest one above it. Because more than one ID is being pulled
from the database at a time, they have to be renamed so they don't conflict.

Please tell me if I am missing something here. (BTW, the image's ID will
already be known. What is being pulled are the ID's for the prev and
next images, so they can be linked to and provide a continuous image
series.)

....Geshel
--
***********************************************************************
* My reply-to is an automatically monitored spam honeypot. Do not use *
* it unless you want to be blacklisted by SpamCop. Please reply to my *
* first name at my last name dot org. *
***********************************************************************

 

[Onin Tayson]

yep, i missed the ORDER BY ("SORT") keyword (i just typed it here directly
as what i'm doing right now . but it should be the other way around (based
on reading the SQL - no testing done here). the Previous ID should be
ORDERed BY [ID] DESC and Next ID should be ordered ASC. below should be
your SQL code.

SELECT [ID] FROM [tblImages] WHERE [ID] = @id UNION
SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]<@id ORDER BY [ID] DESC UNION
SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]>@id ORDER BY [ID]

regarding column renaming... you don't have to rename the UNIONed columns as
it will have no effect on the results of the query, it will still use the
column name from the first SELECT.

HTH,

you can use the TOP & UNION keywords

select id from table where id = @id union
select top 1 id from table where id<@id union
select top 1 id from table where id>@id

HTH,

(bottom-posted for clarity: http://www.caliburn.nl/topposting.html)

You know, I didn't think of that at all. Although I am curious, the way I
read the third line, it wouldn't bring me #25, but rather the last ID (the
highest numbered ID) in the list of images.

Although I'm not an SQL guru, shouldn't the second and third lines be
ORDER BY [ID], where the second line is normal (ASC) and the third line is
reversed (DESC)? That way, the second line has the highest number under 22
at the top, and the third line has the lowest number over 22 at the top.

As well, since three ID's are being returned, they'll have to be renamed
before being plunked into the repeater, no?

I see the query as being:

SELECT TOP 1 [ID] AS [prev] FROM [tblImages] WHERE [ID]<@id SORT BY [ID]
ASC UNION
SELECT TOP 1 [ID] AS [next] FROM [tblImages] WHERE [ID]>@id SORT BY [ID]
DESC

This way, the ID of the image requested is being used to pull two more
ID's from the database. One ID is the highest one beneath it, the other is
the lowest one above it. Because more than one ID is being pulled from the
database at a time, they have to be renamed so they don't conflict.

Please tell me if I am missing something here. (BTW, the image's ID will
already be known. What is being pulled are the ID's for the prev and next
images, so they can be linked to and provide a continuous image series.)

...Geshel
--
***********************************************************************
* My reply-to is an automatically monitored spam honeypot. Do not use *
* it unless you want to be blacklisted by SpamCop. Please reply to my *
* first name at my last name dot org. *
***********************************************************************

 

[Neo Geshel]

I have an Access DB, from which I am going to pull images. Each imagehas
an associated ID, but the ID's are not necessarily sequential (some
images may have been deleted, leaving gaps in the list of ID's).

I am looking to be able to call an ID and its Image from the database,
but also have returned the previous ID and the next ID, even when those
actual ID's are not necessarily +1 and -1 to the ID being called.

For example, I want to bring up an image with the ID of 22. There *had*
been images with ID's of 21, 23 and 24, but they had been deleted in the
past. I want to be able to have returned, in the same query (or be able
to immediately use another query to discover them) the ID's of 20 and25,
because these are the ID's of currently existing photos that are directly
before and after the photo that has the ID of 22.

Is there anywhere where I can see and example that makes use of images
stored in a database?

you can use the TOP & UNION keywords

select id from table where id = @id union
select top 1 id from table where id<@id union
select top 1 id from table where id>@id

You know, I didn't think of that at all. Although I am curious, the wayI
read the third line, it wouldn't bring me #25, but rather the last ID (the
highest numbered ID) in the list of images.

Although I'm not an SQL guru, shouldn't the second and third lines be
ORDER BY [ID], where the second line is normal (ASC) and the third lineis
reversed (DESC)? That way, the second line has the highest number under22
at the top, and the third line has the lowest number over 22 at the top.

As well, since three ID's are being returned, they'll have to be renamed
before being plunked into the repeater, no?

I see the query as being:

SELECT TOP 1 [ID] AS [prev] FROM [tblImages] WHERE [ID]<@id SORT BY [ID]
ASC UNION
SELECT TOP 1 [ID] AS [next] FROM [tblImages] WHERE [ID]>@id SORT BY [ID]
DESC

This way, the ID of the image requested is being used to pull two more
ID's from the database. One ID is the highest one beneath it, the otheris
the lowest one above it. Because more than one ID is being pulled from the
database at a time, they have to be renamed so they don't conflict.

Please tell me if I am missing something here. (BTW, the image's ID will
already be known. What is being pulled are the ID's for the prev and next
images, so they can be linked to and provide a continuous image series.)

yep, i missed the ORDER BY ("SORT") keyword (i just typed it here directly
as what i'm doing right now . but it should be the other way around (based
on reading the SQL - no testing done here). the Previous ID should be
ORDERed BY [ID] DESC and Next ID should be ordered ASC. below should be
your SQL code.

SELECT [ID] FROM [tblImages] WHERE [ID] = @id UNION
SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]<@id ORDER BY [ID] DESC UNION
SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]>@id ORDER BY [ID]

regarding column renaming... you don't have to rename the UNIONed columns as
it will have no effect on the results of the query, it will still use the
column name from the first SELECT.

(bottom-posted for clarity: http://www.caliburn.nl/topposting.html)

You are correct about the sorting of the ID's - I wasn't thinking straight.

But as for the UNIONed columns, I thought I would *have* to rename them,
because all three [ID]'s would be going into the same Repeater, no? If I
didn't rename them, how would the Repeater know which <%#
Container.DataItem("ID") %> to attach each of them to? I intend to have
at least three of these containers inside the Repeater - one for the
"previous" link (which reloads the page, but with the previous image's
ID as a variable in the URL), one for the current image (so the
image.aspx page being called inside the <img> tag knows which image to
get and load from the DB), and one for the "next" link (which, once
again, reloads the current page, but with the next image's ID as a
variable).

Although, I have to say, thanks for your help, Onin! I don't think I
would have figured this out on my own.

...Geshel
--
***********************************************************************
* My reply-to is an automatically monitored spam honeypot. Do not use *
* it unless you want to be blacklisted by SpamCop. Please reply to my *
* first name at my last name dot org. *
***********************************************************************
“Anyone who believes in Intelligent Design (“creationismâ€) is just as
ignorant and ill-educated as someone who believes that the world is
flat, that the Sun circles the Earth or that there really is a tooth
fairy. Darwinism has an overwhelming foundation of evidence that can be
tested and reproduced. Intelligent Design, on the other hand, has no
evidence at all; not one single shred of testable proof. As such,
Intelligent Design is Religious Mythology, and has no right whatsoever
to be in our Science classrooms.†- 99.99+% of Scientists
***********************************************************************
Mignon McLaughlin once said that “A nymphomaniac is a woman [who is] as
obsessed with sex as the average man.†Unfortunately, since true
nymphomaniacs are so rare, this means that it takes an extraordinary
woman to keep up with an ordinary man.
***********************************************************************

 

[KitWest]

I thought UNION'd queries supported only a single ORDER BY at the end,
determining the order of the entire union'd set of records. If you run
into this, you could try subqueries:
SELECT [ID] FROM [tblImages] WHERE [ID] = @id UNION
SELECT * FROM (SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]<@id ORDER
BY [ID] DESC) alias1 UNION
SELECT * FROM (SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]>@id ORDER
BY [ID]) alias2

 

[Neo Geshel]

I thought UNION'd queries supported only a single ORDER BY at the end,
determining the order of the entire union'd set of records. If you run
into this, you could try subqueries:
SELECT [ID] FROM [tblImages] WHERE [ID] = @id UNION
SELECT * FROM (SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]<@id ORDER
BY [ID] DESC) alias1 UNION
SELECT * FROM (SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]>@id ORDER
BY [ID]) alias2

COOL!

I'll be trying it out when I build my Image Gallery viewer in a couple
of hours. Hopefully it'll work flawlessly.

Thanks!
...Geshel
--
***********************************************************************
* My reply-to is an automatically monitored spam honeypot. Do not use *
* it unless you want to be blacklisted by SpamCop. Please reply to my *
* first name at my last name dot org. *
***********************************************************************
“Anyone who believes in Intelligent Design (“creationismâ€) is just as
ignorant and ill-educated as someone who believes that the world is
flat, that the Sun circles the Earth or that there really is a tooth
fairy. Darwinism has an overwhelming foundation of evidence that can be
tested and reproduced. Intelligent Design, on the other hand, has no
evidence at all; not one single shred of testable proof. As such,
Intelligent Design is Religious Mythology, and has no right whatsoever
to be in our Science classrooms.†- 99.99+% of Scientists
***********************************************************************
Mignon McLaughlin once said that “A nymphomaniac is a woman [who is] as
obsessed with sex as the average man.†Unfortunately, since true
nymphomaniacs are so rare, this means that it takes an extraordinary
woman to keep up with an ordinary man.
***********************************************************************

 

[Andrew Morton]

I thought UNION'd queries supported only a single ORDER BY at the
end, determining the order of the entire union'd set of records. If
you run into this, you could try subqueries:
SELECT [ID] FROM [tblImages] WHERE [ID] = @id UNION
SELECT * FROM (SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]<@id
ORDER BY [ID] DESC) alias1 UNION
SELECT * FROM (SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]>@id
ORDER BY [ID]) alias2

For the next ID, isn't is simpler to get the minimum ID larger than the
specified one?

SELECT MIN(ID) FROM [tblImages] WHERE ID>@id;

And similarly using MAX for the previous ID.

If there were a lot of values and "holes" were rare, perhaps it would be
better to check to find if the next value (ID+1) exists before making it
search everything.

Andrew

 

[Neo Geshel]

I thought UNION'd queries supported only a single ORDER BY at the
end, determining the order of the entire union'd set of records. If
you run into this, you could try subqueries:
SELECT [ID] FROM [tblImages] WHERE [ID] = @id UNION
SELECT * FROM (SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]<@id
ORDER BY [ID] DESC) alias1 UNION
SELECT * FROM (SELECT TOP 1 [ID] FROM [tblImages] WHERE [ID]>@id
ORDER BY [ID]) alias2

For the next ID, isn't is simpler to get the minimum ID larger than the
specified one?

SELECT MIN(ID) FROM [tblImages] WHERE ID>@id;

And similarly using MAX for the previous ID.

If there were a lot of values and "holes" were rare, perhaps it would be
better to check to find if the next value (ID+1) exists before making it
search everything.

Yes, but will it work with Access? This is only a small site, I don't
need SQL Server just to make the SQL queries work.

...Geshel
--
***********************************************************************
* My reply-to is an automatically monitored spam honeypot. Do not use *
* it unless you want to be blacklisted by SpamCop. Please reply to my *
* first name at my last name dot org. *
***********************************************************************
“Anyone who believes in Intelligent Design (“creationismâ€) is just as
ignorant and ill-educated as someone who believes that the world is
flat, that the Sun circles the Earth or that there really is a tooth
fairy. Darwinism has an overwhelming foundation of evidence that can be
tested and reproduced. Intelligent Design, on the other hand, has no
evidence at all; not one single shred of testable proof. As such,
Intelligent Design is Religious Mythology, and has no right whatsoever
to be in our Science classrooms.†- 99.99+% of Scientists
***********************************************************************
Mignon McLaughlin once said that “A nymphomaniac is a woman [who is] as
obsessed with sex as the average man.†Unfortunately, since true
nymphomaniacs are so rare, this means that it takes an extraordinary
woman to keep up with an ordinary man.
***********************************************************************

 

[Andrew Morton]

For the next ID, isn't is simpler to get the minimum ID larger than
the specified one?

SELECT MIN(ID) FROM [tblImages] WHERE ID>@id;

Yes, but will it work with Access? This is only a small site, I don't
need SQL Server just to make the SQL queries work.

Ooh, didn't check that bit. I don't have Access here. What happens when you
try it on your test copy of the database?

Andrew

 

[Onin Tayson]

i think it will work. btw, i tested the SQL i sent you and it's not not
working properly... sorry 'bout that. I tried the code below (using SQL
Server though) and i think it's what you're looking for...

select [ID] from [tblImages] where [ID] = @ID union
select max([ID]) from [tblImages] where [ID] < @ID union
select min([ID]) from [tblImages] where [ID] > @ID

HTH,

For the next ID, isn't is simpler to get the minimum ID larger than
the specified one?

SELECT MIN(ID) FROM [tblImages] WHERE ID>@id;

Yes, but will it work with Access? This is only a small site, I don't
need SQL Server just to make the SQL queries work.

Ooh, didn't check that bit. I don't have Access here. What happens when
you try it on your test copy of the database?

Andrew

 

[Neo Geshel]

Andrew Morton wrote:
For the next ID, isn't is simpler to get the minimum ID larger than
the specified one?

SELECT MIN(ID) FROM [tblImages] WHERE ID>@id;

Yes, but will it work with Access? This is only a small site, I don't
need SQL Server just to make the SQL queries work.

Ooh, didn't check that bit. I don't have Access here. What happens when
you try it on your test copy of the database?

i think it will work. btw, i tested the SQL i sent you and it's not not
working properly... sorry 'bout that. I tried the code below (using SQL
Server though) and i think it's what you're looking for...

select [ID] from [tblImages] where [ID] = @ID union
select max([ID]) from [tblImages] where [ID] < @ID union
select min([ID]) from [tblImages] where [ID] > @ID

I've actually broken up all three queries, so they work independant of
each other. I still have to test them properly, as I don't have a
reliable way of inserting the name of the table into the stream
extracted from the database and sent on to the Repeater. The Repeater is
used to build the HTML code for bringing in the Image Viewer, which is
another aspx page which draws the image from the database.
Unfortunately, this image viewer requires the table name in which the
image rests, and I don't know how to dynamically insert this into the
data sent to the Repeater.

If you are confused, think of the set up this way: Many pages that have
image galleries all reference different tables that contain images. Each
table that contain images have the same columns as the others, namely ID
and Comment, but the tables themselves are named differently from each
other (duh!).

The problem arises because I want to have one Gallery Viewer that all
these different galleries can reference. The galleries are simple pages
filled with thumbnails linked to the gallery viewer. The gallery viewer
is a single page with the current image at full size, and a prev and a
next thumbnail image. The images are drawn from the database via an aspx
page that requires the database name and the ID of the image itself.

I need to have the table name preserved across two "hops" - one from the
thumbnail link to the Gallery Viewer, and then from the Gallery Viewer
to the ImageViewer.aspx page. As such, because the three images on the
Gallery Viewer are "determined" by your SQL, and then assembled by a
Repeater, I need to have name of the table inserted into the HTML that
references the Image Viewer aspx file so it knows which table to grab.

FYI, I've made another post asking for just this info.
...Geshel
--
***********************************************************************
* My reply-to is an automatically monitored spam honeypot. Do not use *
* it unless you want to be blacklisted by SpamCop. Please reply to my *
* first name at my last name dot org. *
***********************************************************************
“Anyone who believes in Intelligent Design (“creationismâ€) is just as
ignorant and ill-educated as someone who believes that the world is
flat, that the Sun circles the Earth or that there really is a tooth
fairy. Darwinism has an overwhelming foundation of evidence that can be
tested and reproduced. Intelligent Design, on the other hand, has no
evidence at all; not one single shred of testable proof. As such,
Intelligent Design is Religious Mythology, and has no right whatsoever
to be in our Science classrooms.†- 99.99+% of Scientists
***********************************************************************
Mignon McLaughlin once said that “A nymphomaniac is a woman [who is] as
obsessed with sex as the average man.†Unfortunately, since true
nymphomaniacs are so rare, this means that it takes an extraordinary
woman to keep up with an ordinary man.
***********************************************************************