SQL Query based on ROW_Number not working
Basically I am trying to do a query to a page that holds just an image. I need to provide the tripID number and then the ROWID (as there coul开发者_StackOverflow社区d be multiple images) to receive a single image. I will be looping until each image is in its corresponding image box in html.
This code doesn't seem to work(I get Invalid column name 'ROWID'), but if I remove the AND ROWID='1' it returns all the images and its row id like this:
ROWID PHOTO
1 32jjr3h2jh23hj4h32jh42ll23j42
2 HU8308DJAOID9ASIDJI32C89EE29
-
Select ROW_NUMBER() OVER (ORDER BY Photo ASC) AS ROWID, TBL_Photo.Photo
From TBL_Photo
left join TBL_TripDetails
ON TBL_Photo.TripID=TBL_TripDetails.pkiTripID
Where pkiTripID = '121' AND ROWID = '1'
You can't reference a column alias in the WHERE clause -- you need to use a subquery or a CTE:
Subquery Example:
SELECT x.rowid,
x.photo
FROM (SELECT ROW_NUMBER() OVER (ORDER BY p.photo) AS ROWID,
p.photo
FROM TBL_PHOTO p
LEFT JOIN TBL_TRIPDETAILS td ON td.pkitripid = p.tripid
WHERE td.pkiTripID = '121') x
WHERE x.rowid = 1
CTE example:
WITH example AS (
SELECT ROW_NUMBER() OVER (ORDER BY p.photo) AS ROWID,
p.photo
FROM TBL_PHOTO p
LEFT JOIN TBL_TRIPDETAILS td ON td.pkitripid = p.tripid
WHERE td.pkiTripID = '121')
SELECT x.rowid,
x.photo
FROM example x
WHERE x.rowid = 1
Performance
There's no performance difference between the two options, but the WITH syntax isn't supported on all databases.
精彩评论