SQL Row_Number() Function in Where Clause Without Order By

ROW_NUMBER Without ORDER BY

There is no need to worry about specifying constant in the ORDER BY expression. The following is quoted from the Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions written by Itzik Ben-Gan (it was available for free download from Microsoft free e-books site):

As mentioned, a window order clause is mandatory, and SQL Server
doesn’t allow the ordering to be based on a constant—for example,
ORDER BY NULL. But surprisingly, when passing an expression based on a
subquery that returns a constant—for example, ORDER BY (SELECT
NULL)—SQL Server will accept it. At the same time, the optimizer
un-nests, or expands, the expression and realizes that the ordering is
the same for all rows. Therefore, it removes the ordering requirement
from the input data. Here’s a complete query demonstrating this
technique:

SELECT actid, tranid, val,
 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM dbo.Transactions;

Observe in the properties of the Index Scan iterator that the Ordered
property is False, meaning that the iterator is not required to return
the data in index key order

---

The above means that when you are using constant ordering is not performed. I will strongly recommend to read the book as Itzik Ben-Gan describes in depth how the window functions are working and how to optimize various of cases when they are used.

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

SQL Server 2005 ROW_NUMBER() without ORDER BY

You can avoid specifying an explicit ordering as follows:

INSERT dbo.TargetTable (ID, FIELD)
SELECT
   Row_Number() OVER (ORDER BY (SELECT 1))
      + Coalesce(
         (SELECT Max(ID) FROM dbo.TargetTable WITH (TABLOCKX, HOLDLOCK)),
         0
      ),
   FieldValue
FROM dbo.SourceTable
WHERE {somecondition};

However, please note that is merely a way to avoid specifying an ordering and does NOT guarantee that any original data ordering will be preserved. There are other factors that can cause the result to be ordered, such as an ORDER BY in the outer query. To fully understand this, one must realize that the concept "not ordered (in a particular way)" is not the same as "retaining original order" (which IS ordered in a particular way!). I believe that from a pure relational database perspective, the latter concept does not exist, by definition (though there may be database implementations that violate this, SQL Server is not one of them).

The reason for calculating the Max in the query and for adding the lock hints is to prevent errors due to a concurrent process inserting using the same value you plan to use, in between the parts of the query executing. The only other semi-reasonable workaround would be to perform the Max() and INSERT in a loop some number of times until it succeeds (still far from an ideal solution). Using an identity column is far superior. It's not good for concurrency to exclusively lock entire tables, and that is an understatement.

Note: Many people use (SELECT NULL) to get around the "no constants allowed in the ORDER BY clause of a windowing function" restriction. For some reason, I prefer 1 over NULL. What you use is up to you.

SQL Server : Row Number without ordering

Using ORDER BY (SELECT NULL) will give you the results your looking for.

SELECT

VariableName,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM

SeanVault.dbo.TempVarIDs

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.

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;

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;

---

Related Topics