score:2

Accepted answer

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

Read More

score:0

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

score:0

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


More questions with similar tag