Query of a query

[Galway]

Hey all. I'm a bit stumped on this particular query (I've been doing it for quite a while though). I could actually be asking the wrong question because I'm taking the wrong approach completely but here is what I'm trying:

Basically, I'm making a photo album where you go through each photo one at a time (but there's multiple users so multiple albums).

I want to let the user know what picture of his total pictures he's viewing (like 1 of 24). Current query:

select ROW_NUMBER() over (order by UserId) as RowId, * from tblPhotos
where UserId = '$UserId' so it looks like:

RowId PhotoId UserId Location
1 1 1 http://...
2 2 1 http://...
3 3 1 http://...
4 7 1 http://...
5 9 1 http://...


*Edit: Sorry for the horrible table, it looks better in my comment box lol*

There's a jump in PhotoId's because the user can delete them as well (in this case he's deleted (4-6 & 8).

So that's my query on each page. But now I need to be able to get the Location and RowId given a specific PhotoId and UserId but I can't just do something like

select ROW_NUMBER() over (order by UserId) as RowId, * from tblPhotos
where UserId = 1 and PhotoId =3

because that would make the RowId = 1 when it's supposed to be 3

Any ideas here?

[SDas]

Galway. If PhotoId and UserId is a unique combination, just select that from the table (don't use ROW_NUMBER()).

If you have to have the same RowId, you can do just what you ask. Select from a Select.

SELECT *
FROM (select ROW_NUMBER() over (order by UserId) as RowId
           , * 
      from tblPhotos
      where UserId = '$UserId'
     ) as Derived_table
WHERE PhotoId = 3 
  AND UserId = 1

[Galway]

This worked beautifully.

Just one more quick question for those who know a little php:

I have links going to the next photo and previous photo. But as of right now I have the set to just

next = currentPhotoId + 1
previous = currentPhotoId - 1

but for reasons mentioned in my first question, this won't work since users will be deleting photos all the time. I need the next row and previous row of the query. I can think of some really inefficient ways to get them. Such as making whole new queries something like

For Next Photo:
SELECT TOP 1 * from tblPhotos WHERE UserId = $_GET[UserId] and PhotoId > $currentPhotoId

and then make another similar to this for previous photo

is this really the best way to do this?