SQL Row_Number() Function in Where Clause

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:































pcabr
pc1a1b11
pc1a2b2null
pc1a3b32

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.

  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER 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



Leave a reply



Submit