SQL ROW_NUMBER OVER syntax

Question

I have this:

SELECT ROW_NUMBER() OVER (ORDER BY vwmain.ch) as RowNumber,
    vwmain.vehicleref,vwmain.capid,
    vwmain.manufacturer,vwmain.model,vwmain.derivative,
    vwmain.isspecial,
    vwmain.created,vwmain.updated,vwmain.stocklevel,
    vwmain.[type],
    vwmain.ch,vwmain.co2,vwmain.mpg,vwmain.term,vwmain.milespa
FROM vwMain_LATEST vwmain 
INNER JOIN HomepageFeatured 
  on vwMain.vehicleref = homepageFeatured.vehicleref 
WHERE homepagefeatured.siteskinid = 1
  AND homepagefeatured.Rotator = 1
  AND RowNumber = 1
ORDER BY homepagefeatured.orderby

It fails on "Invalid column name RowNumber"

Not sure how to prefix it to access it?

Thanks

You just want the first row? A `top` or `limit` might be simpler. — Kevin Seifert, Mar 11 '15 at 14:09
You can't use the alias of a column directly on the `WHERE`, you either use a derived table `SELECT .... FROM () T WHENRE RowNumber = 1` or you can use a `CTE`. Also, @user1389596 is right, you should just use `TOP 1` — Lamak, Mar 11 '15 at 14:10
I'm guessing the OP is probably trying to use this to remove duplicates... in which case you'll need `partition by`.. .instead of `TOP` — sam yi, Mar 11 '15 at 14:19

sgeddes · Accepted Answer · 2015-03-11T14:13:44.787

You can't reference the field like that. You can however use a subquery or a common-table-expression:

Here's a subquery:

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY vwmain.ch) as RowNumber,
        vwmain.vehicleref,vwmain.capid,
        vwmain.manufacturer,vwmain.model,vwmain.derivative,
        vwmain.isspecial,
        vwmain.created,vwmain.updated,vwmain.stocklevel,
        vwmain.[type],
        vwmain.ch,vwmain.co2,vwmain.mpg,vwmain.term,vwmain.milespa,
        homepagefeatured.orderby
    FROM vwMain_LATEST vwmain 
        INNER JOIN HomepageFeatured on vwMain.vehicleref = homepageFeatured.vehicleref 
    WHERE homepagefeatured.siteskinid = 1
       AND homepagefeatured.Rotator = 1
) T
WHERE RowNumber = 1
ORDER BY orderby

Rereading your query, since you aren't partitioning by any fields, the order by at the end is useless (it contradicts the order of the ranking function). You're probably better off using top 1...

Better you can add the `TOP 1` answer. – Pரதீப் Mar 11 '15 at 14:22 — Pரதீப், Mar 11 '15 at 14:22

score 0 · Answer 2 · answered Mar 11 '15 at 14:14

Using top:

  SELECT top 1 vwmain.vehicleref,vwmain.capid,
        vwmain.manufacturer,vwmain.model,vwmain.derivative,
        vwmain.isspecial,
        vwmain.created,vwmain.updated,vwmain.stocklevel,
        vwmain.[type],
        vwmain.ch,vwmain.co2,vwmain.mpg,vwmain.term,vwmain.milespa,
        homepagefeatured.orderby
    FROM vwMain_LATEST vwmain 
        INNER JOIN HomepageFeatured on vwMain.vehicleref =   homepageFeatured.vehicleref 
    WHERE homepagefeatured.siteskinid = 1
       AND homepagefeatured.Rotator = 1
    ORDER BY homepagefeatured.orderby

May be `ORDER BY vwmain.ch ASC` will make more sense – Pரதீப் Mar 11 '15 at 14:17 — Pரதீப், Mar 11 '15 at 14:17

score 0 · Answer 3 · edited May 23 '17 at 10:24

(EDIT: This answer is a nearby pitfall. I leave it for documentation.)

Have a look here: Referring to a Column Alias in a WHERE Clause

This is the same situation.

It is a matter of how the sql query is parsed/compiled internally, so your field alias names are not known at the time the where clause is interpreted. Therefore you might try, in reference to the example above:

SELECT ROW_NUMBER() OVER (ORDER BY vwmain.ch) as RowNumber,
vwmain.vehicleref,vwmain.capid, vwmain.manufacturer,vwmain.model,vwmain.derivative, vwmain.isspecial,vwmain.created,vwmain.updated,vwmain.stocklevel, vwmain.[type],
vwmain.ch,vwmain.co2,vwmain.mpg,vwmain.term,vwmain.milespa

FROM vwMain_LATEST vwmain 
  INNER JOIN HomepageFeatured on vwMain.vehicleref = homepageFeatured.vehicleref 

WHERE homepagefeatured.siteskinid = 1
  AND homepagefeatured.Rotator = 1
  AND ROW_NUMBER() OVER (ORDER BY vwmain.ch) = 1

ORDER BY homepagefeatured.orderby

Thus you see your expression in the select statement is exactly reused in the where clause.

So this is a wrong answer. Thanks. I won't delete the question so others may see the pitfall. — peter_the_oak, Mar 11 '15 at 14:17

SQL ROW_NUMBER OVER syntax

3 Answers3