Select next item in DB without knowing what ID it has
 

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. *
************************************************** *********************

Re: Select next item in DB without knowing what ID it has
 

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" <gotcha@geshel.org> wrote in message
news:evlOppb5FHA.2864@tk2msftngp13.phx.gbl...
>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. *
> ************************************************** *********************

Re: Select next item in DB without knowing what ID it has
 

Onin Tayson wrote:
> "Neo Geshel" <gotcha@geshel.org> wrote in message
> news:evlOppb5FHA.2864@tk2msftngp13.phx.gbl...
>
>>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. *
>>************************************************ ***********************
>
>
> 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. *
************************************************** *********************

Re: Select next item in DB without knowing what ID it has
 

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,

"Neo Geshel" <gotcha@geshel.org> wrote in message
news:eBn18tc5FHA.2560@TK2MSFTNGP12.phx.gbl...
> Onin Tayson wrote:
>> "Neo Geshel" <gotcha@geshel.org> wrote in message
>> news:evlOppb5FHA.2864@tk2msftngp13.phx.gbl...
>>
>>>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. *
>>>*********************************************** ************************
>>
>>
>> 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. *
> ************************************************** *********************

Re: Select next item in DB without knowing what ID it has
 

Onin Tayson wrote:
> "Neo Geshel" <gotcha@geshel.org> wrote in message
> news:eBn18tc5FHA.2560@TK2MSFTNGP12.phx.gbl...
>>Onin Tayson wrote:
>>>"Neo Geshel" <gotcha@geshel.org> wrote in message
>>>news:evlOppb5FHA.2864@tk2msftngp13.phx.gbl...
>>>>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.
************************************************** *********************

Re: Select next item in DB without knowing what ID it has
 

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

Re: Select next item in DB without knowing what ID it has
 

KitWest wrote:
> 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.
************************************************** *********************

Re: Select next item in DB without knowing what ID it has
 

Neo Geshel wrote:
> KitWest wrote:
>> 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

Re: Select next item in DB without knowing what ID it has
 

Andrew Morton wrote:
> Neo Geshel wrote:
>>KitWest wrote:
>>>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.
************************************************** *********************

Re: Select next item in DB without knowing what ID it has
 

Neo Geshel wrote:
> 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?

Andrew