Referencing ROW_NUMBER() in WHERE
You are not using MySQL. In order to do this, use a CTE or subquery:
SELECT s.*
FROM (SELECT mt.Name, mt.IDNO, so.IDType, Row = ROW_NUMBER() OVER (ORDER BY mt.Name)
FROM MainTable mt LEFT JOIN
SupportTable1 so
ON so.Name = mt.Name LEFT JOIN
SupportTable2 st
ON st.Name = mt.Name
) s
WHERE Row BETWEEN 1 and 100000;
Notes:
- Window functions cannot be used in the
WHERE
clause. - Column aliases cannot be used in the
WHERE
clause either; that is why a CTE or subquery is needed. - Don't put single quotes around integer constants.
Alternatively, you can just use TOP
:
SELECT TOP (100000) mt.Name, mt.IDNO, so.IDType, Row = ROW_NUMBER() OVER (ORDER BY mt.Name)
FROM MainTable mt LEFT JOIN
SupportTable1 so
ON so.Name = mt.Name LEFT JOIN
SupportTable2 st
ON st.Name = mt.Name
ORDER BY Row;
Window function with where condition (conditional row_number())
Use row_number() within a "case when" statement with a second case statement in the "partition by" as below:
(Case condition when true then ROW_NUMBER() OVER(
PARTITION BY (case condition when true then pc end)
ORDER BY
a DESC, b DESC
)
end)r
Example:
create table sampledata(PC varchar(10), A varchar(10), B varchar(10));
insert into sampledata values('pc1', 'a1', 'b1');
insert into sampledata values('pc1', 'a2', 'b2');
insert into sampledata values('pc1', 'a3', 'b3');
Query:
select *,(Case when A<>'a2' then ROW_NUMBER() OVER(
PARTITION BY (case when A<>'a2' then pc end)
ORDER BY a , b DESC
)
end)r
from sampledata order by a, b desc
Output:
pc | a | b | r |
---|---|---|---|
pc1 | a1 | b1 | 1 |
pc1 | a2 | b2 | null |
pc1 | a3 | b3 | 2 |
Using ROW_NUMBER() with a where clause to get the row number in the unfiltered table
Window functions being analytical, ad-hoc type of calculations run on the current scope of the retrieved data. Unlike lexical order of most programming languages, SQL runs in logical order and not in the order commands are declared. Hence, FROM
, JOIN
, WHERE
clauses are usually the first steps run in an SQL query. Therefore, once the data is retrieved, then window functions are calculated on that scope of data.
In fact SQLite's window function docs intimate this (emphasis added):
A window function is an SQL function where the input values are taken from a "window" of one or more rows in the results set of a SELECT statement.
Instead of the self-join solution you arrived at (which uses the outmoded implicit and not explicit join), you could have resolved your needs with CTE or subquery:
WITH sub AS (
SELECT
ROW_NUMBER() OVER ( ORDER BY x ) AS row_num
, x
, y
FROM
t
)
SELECT
sub.row_num
, sub.x
, sub.y
FROM
sub
WHERE
sub.x = 3;
SELECT sub.row_num
, sub.x
, sub.y
FROM (
SELECT
ROW_NUMBER() OVER ( ORDER BY x ) AS row_num
, x
, y
FROM
t
) sub
WHERE
sub.x = 3;
Row_number function using directly
First, you want dense_rank()
, not row_number()
if you want the second highest value -- ties might get in the way otherwise.
You can use an arithmetic trick:
SELECT TOP (1) WITH TIES id, salary, depid
FROM emp
ORDER BY ABS(DENSE_RANK() over (PARTITION BY depid ORDER BY salary DESC) - 2)
The "-2" is an arithmetic trick to put the "second" values highest.
That said, I would stick with the subquery because the intent in clearer.
How to have where clause on row_number within the same select statement?
SQL order of execution.
FROM
clauseWHERE
clauseGROUP
BY clauseHAVING
clauseSELECT
clauseORDER BY
clause
the check_row alias was made in the select
part so it doesn't exist yet in the context
EDIT
done some testing. can't seem to get it right. as a temporary solution you could attempt to put the
ROW_NUMBER() over(...
in the where
clause aswell
EDIT:
another option from the MSDN website is
Returning a subset of rows
The following example calculates row numbers for all rows in the SalesOrderHeader table in the order of the OrderDate and returns only rows 50 to 60 inclusive.
USE AdventureWorks2012;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber
FROM Sales.SalesOrderHeader
)
SELECT SalesOrderID, OrderDate, RowNumber
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;
SQL Row_Number() function in Where Clause without ORDER BY?
Just in case it is useful to someone else. I just figured it out from elsewhere:
WITH MyCte AS
(
select employee_id,
RowNum = row_number() OVER (ORDER BY (SELECT 0))
from V_EMPLOYEE
ORDER BY Employee_ID
)
SELECT employee_id
FROM MyCte
WHERE RowNum > 0
Select stmt with ROW_NUMBER() OVER(PARTITION BY...) in Where clause
You cannot use window functions in the Where
clause(only in SELECT
or ORDER BY
).
But you could use a CTE instead:
WITH CTE
AS (SELECT fs.docu_id AS docID,
fs.field_3 AS ProductNo,
fs.field_4 AS [Status],
fs.field_5 AS [Index],
pd.[doku_nr] AS docIDshort,
Row_number() OVER(
Partition BY fs.field_3
ORDER BY fs.field_5 DESC) AS rownum
FROM [table1] fs
JOIN [table2] pd
ON fs.docu_id = pd.docu_id
AND fs.field_4 = 'valid')
SELECT docid, productno, status, [index], docIDshort
FROM CTE
WHERE rownum = 1
Use ROW_NUMBER() alias in WHERE
Using subquery:
SELECT
*
FROM
(SELECT
inv.client_pk, inv.invoice_pk, inv.contract_pk,
ROW_NUMBER() OVER (PARTITION BY inv.client_pk ORDER BY inv.client_pk) AS row_number
FROM
controllr.client as cli
LEFT JOIN
controllr.invoice as inv ON inv.client_pk = cli.client_pk
WHERE
client_status != 3) AS sub
WHERE
row_number <= 3;
Using CTE:
WITH cte AS
(
SELECT
inv.client_pk, inv.invoice_pk, inv.contract_pk,
ROW_NUMBER() OVER ( PARTITION BY inv.client_pk ORDER BY inv.client_pk) AS row_number
FROM
controllr.client as cli
LEFT JOIN
controllr.invoice as inv ON inv.client_pk = cli.client_pk
WHERE
client_status != 3
)
SELECT *
FROM cte
WHERE row_number <= 3;
The reason why you are receiving that error is because the WHERE
clause is processed before the SELECT
clause. Therefore, the engine is unable to see row_number
as a column when trying to process the condition ... row_number <= 3
with your original query.
Also, using CTE has the same performance of using a subquery but it does improve readability.
Selecting row with ROW_NUMBER() window function
Your issue is that rowCol
is an alias for a window function (ROW_NUMBER()
) and they cannot appear in a WHERE
clause. You can use a QUALIFY
clause instead:
SELECT Employee.Salary_Grade_Id,
SUM(Salary_Grades.Grade_Amount) AS total,
ROW_NUMBER() OVER(ORDER BY Employee.Salary_Grade_Id) AS rowCol
FROM Employee, Salary_Grades
WHERE (Employee.Salary_Grade_Id = Salary_Grades.Grade_Id)
GROUP BY Employee.Salary_Grade_Id
QUALIFY rowCol = 1
Note that you should use explicit JOIN
syntax and rewrite the query as
SELECT Employee.Salary_Grade_Id,
SUM(Salary_Grades.Grade_Amount) AS total,
ROW_NUMBER() OVER(ORDER BY Employee.Salary_Grade_Id) AS rowCol
FROM Employee
JOIN Salary_Grades ON Employee.Salary_Grade_Id = Salary_Grades.Grade_Id
GROUP BY Employee.Salary_Grade_Id
QUALIFY rowCol = 1
Related Topics
How to Select Only the First Rows for Each Unique Value of a Column
Doesn't Linq to SQL Miss the Point? Aren't Orm-Mappers (Subsonic, etc.) Sub-Optimal Solutions
SQL Update Fields of One Table from Fields of Another One
Repeat Rows N Times According to Column Value
Order by Items Must Appear in the Select List If Select Distinct Is Specified
How to Insert Unicode Text to SQL Server from Query Window
Postgresql Tables Exists, But Getting "Relation Does Not Exist" When Querying
How to Implement a Keyword Search in MySQL
Count Cumulative Total in Postgresql
Select First Record in a One-To-Many Relation Using Left Join
Get the First and Last Date of Next Month in MySQL
How to Execute a Native SQL Script in JPA/Hibernate
Postgresql Join with Array Type with Array Elements Order, How to Implement
Flattening of a 1 Row Table into a Key-Value Pair Table
Conversion Failed When Converting the Varchar Value 'Simple, ' to Data Type Int