SQL Query based on ROW_Number not working

Question

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 could 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'

its not working? What is returned - no rows? And, what is queried out when you exclude ROWID condition from the where clause? — pavanred
– pavanred, Commented Nov 18, 2010 at 18:54
I get Invalid column name 'ROWID'. The ROWID and PHOTO example is queried out if I exclude the ROWID condition — Spooks
– Spooks, Commented Nov 18, 2010 at 18:55

OMG Ponies · Accepted Answer · 2010-11-18 18:56:59Z

5

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.

answered Nov 18, 2010 at 18:56

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Spooks Over a year ago

Subquery worked great, just trying to figure out how it works... x is an object of your subquery, and you can select its attributes..?

OMG Ponies Over a year ago

@Spooks: x is the table alias for the derived table/inline view. Some call it a subquery, but the terminology is vague.

Collectives™ on Stack Overflow

SQL Query based on ROW_Number not working

1 Answer 1

Subquery Example:

CTE example:

Performance

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Subquery Example:

CTE example:

Performance

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related