SQL Row_Number() function in Where Clause

by

To get around this issue, wrap your select statement in a CTE, and then you can query against the CTE and use the windowed function’s results in the where clause.

WITH MyCte AS 
(
    select   employee_id,
             RowNum = row_number() OVER ( order by employee_id )
    from     V_EMPLOYEE 
    ORDER BY Employee_ID
)
SELECT  employee_id
FROM    MyCte
WHERE   RowNum > 0

More Related Contents:

  1. SQL where clause odd behavior [closed]
  2. How do I obtain a Query Execution Plan in SQL Server?
  3. How do I escape a single quote in SQL Server?
  4. Simple way to transpose columns and rows in SQL?
  5. Only inserting a row if it’s not already there
  6. Unpivot with column name
  7. Selecting data from two different servers in SQL Server
  8. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  9. SQL JOIN vs IN performance?
  10. Most efficient way in SQL Server to get date from date+time?
  11. Convert varchar into datetime in SQL Server
  12. TSQL Pivot without aggregate function
  13. How to generate a range of dates in SQL Server
  14. SQL RANK() versus ROW_NUMBER()
  15. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  16. How do I delete from multiple tables using INNER JOIN in SQL server
  17. SQL : BETWEEN vs =
  18. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  19. How do I perform a GROUP BY on an aliased column in SQL Server?
  20. How do I make a composite key with SQL Server Management Studio?
  21. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  22. TransactSQL to run another TransactSQL script
  23. Getting Dates between a range of dates
  24. How do you copy a record in a SQL table but swap out the unique id of the new row?
  25. Using IF ELSE statement based on Count to execute different Insert statements
  26. SQL – Call Stored Procedure for each record
  27. SQL Server SELECT INTO @variable?
  28. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  29. Select a column if other column is null
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment