score:2
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
- Add a row number to result set of a SQL query
- syntax for single row MERGE / upsert in SQL Server
- How to do an inner join on row number in sql server
- How to get the max row number per group/partition in SQL Server?
- How to add row number column in SQL Server 2012
- Selecting specific row number in sql
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
- How to multiply a single row with a number from column in sql
- SQL Server how to update a column for a desired row number
- How to get the current row number in an SQL Server 2000 query?
- SQL Server - group by with row number - Gaps and Islands
- Assigning a Row Number in SQL Server, but grouped on a value
- Count number of times 2 distinct values appear in more than 1 row in SQL Table
- How to get specified number of prior rows from a specified row in SQL Server 2014?
- Invalid SQL syntax when trying to insert a row to the database
- SQL Server Error: maximum number of prefixes. The maximum is 3. with join syntax
- SQL Server 2012: sum over order by gives error Incorrect syntax near 'order'
- Dynamically get row number interval based on specified number in SQL Server
- Partition a row over (num) rows in SQL Server
- Row number over a partition and sliding window
- How to add serial number over a randomly generated resultset in SQL Server?
- Select column with row number concatenated in SQL Server
- How do I generate row number without using rownum() in sql server
- get row number of record in resultset sql server
- SQL Server: CTE, how to get last row number
- Add row number as a new column in SQL Server
- SQL split number over date range
- How to split String after CONCAT over the repetition of Row Number
- How to add a row in SQL Server after Max Rank of a RANK() over partition by(T-SQL)
- Count the number of duplicate values in row in SQL Server
- SQL Server row number generation
More questions with similar tag
- How to union two cte statements in sql with declare ERROR :Incorrect syntax near the keyword 'DECLARE'
- Update trigger for update new table from another
- ADO.NET not properly connecting to database (multiple errors)
- Stored procedure doesn't return correct value
- Connection problems with SQL Server in ASP.NET applications using out-of-process session state
- How to trim everything after certain character in sql
- sort column by string in sql server?
- Cast string to date and change year 0001 to 1900
- Changing orientation of a table in sql
- Must group to Aggregate?
- Pivot and unpivot a table with multiple columns and Rows in SQL
- Changing column properties in SQL Server database in SQL Server Management Studio
- Local and global temporary tables in SQL Server
- Split One table into Two in SQL Server 2008
- SQL syntax issue using "IN" keyword with dynamic data
- Query to find row count of tables based on column name?
- T-SQL query to return row with no connected Users
- How to display query result in new line
- How do I get the char index of an exact word in SQL Server
- SQL Server: if "datetime2" < 1753, then "datetime" NULL?
- The server fail ed to resume the transaction. Desc:9800000002. (3971) (SQLEndTran)')
- How to generate an unordered list from database table data?
- Getting distinct list of ints from 2 distinct int lists
- How to Save TimeSpan value in database in "d.hh:mm:ss" format?
- Data Flow SSIS - Common destination table, different structure flat files
- How does SQL Server handle failed query to linked server?
- Insert rows (if not already exist) to a already existing table (in MS-SQL / T-Sql)
- SQL - Select rows where one column is greater than other column on the same date
- SQL Server: How can I determine if a composite of several fields in a row are unique?
- date type column returning date and time values