2
--1
(select * , row_number() over (order by columnname) as tempcolumn 
 from tablename )

--2
select * , row_number() over (order by defid) as tempcolumn 
from fields 
where tempcolumn between 1 and 2

The first query will give the entire table with a new column added.

I need to display the result after checking the range of the tempcolumn. The second query which i tried throws an error like tempcolumn doesnot exist

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Hari Krishnan
  • 59
  • 4

1 Answers1

2

You could use subquery:

SELECT * FROM (
  select *,ROW_NUMBER() OVER(ORDER BY columnname) as tempcolumn 
  from  tablename) sub
WHERE tempcolumn <= 2;

You cannot use alias from SELECT in WHERE clause at the same level and you cannot use ROW_NUMBER() as part of WHERE.

More info:

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275