Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 660
  • Last Modified:

ROW_NUMBER () function isnt working. (SQL Server 2005)

ROW_NUMBER () function isnt working. (SQL Server 2005)
I never used this function before, so maybe I dont know all details about this function.
I have table1, but I dont have rights to modify this table. This table doesnt have identity column, so for some parsing I have to use ROW_NUMBER () function.
Year is a field with smallint datatype.
I use this code
SELECT *, ROW_NUMBER() OVER (ORDER BY Year) AS 'Row'
FROM table1
WHERE 'Row' BETWEEN 50 and 60
I received this error;
Conversion failed when converting the varchar value 'Row' to data type int.
I used CONVERT function:
SELECT *, ROW_NUMBER() OVER (ORDER BY Year) AS 'Row'
FROM table1
WHERE Convert(int, 'Row') BETWEEN 50 and 60
But again I received the same error.
How can I use ROW_NUMBER() function properly?
Thank you,
J_Kogan
0
J_Kogan
Asked:
J_Kogan
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
;WITH CTE as (
SELECT *, ROW_NUMBER() OVER (ORDER BY Year) AS 'Row'
FROM table1
)
SELECT * from cte
WHERE [ROW] between 50  and 60
0
 
expertsoulCommented:
You cannot put Alias in quotes. Eiether remove the quotes or if you need it keep it in square brackets.



SELECT *, ROW_NUMBER() OVER (ORDER BY Year) AS Row
FROM table1
WHERE Row BETWEEN 50 and 60
 
OR
 
SELECT *, ROW_NUMBER() OVER (ORDER BY Year) AS ['Row']
FROM table1
WHERE ['Row'] BETWEEN 50 and 60

Open in new window

0
 
Anthony PerkinsCommented:
>>You cannot put Alias in quotes. <<
Sure you can.  Though in this case there is no need for quotes or square brackets.  Last I checked Row was not a keyword.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Anthony PerkinsCommented:
Incidentally, using AS ['Row'], gives you a column of:
'Row'

Which I don't believe the author wants.
0
 
SharathData EngineerCommented:

You cannot use the derived column in the WHERE clause. You have to try like this in order to use the derived column.
Or you can try with CTE as aneeshattingal pointed out.
You can go for derived table also if you are not interested in CTE.
SELECT *, ROW_NUMBER() OVER (ORDER BY Year) AS [Row]
FROM table1
WHERE ROW_NUMBER() OVER (ORDER BY Year) BETWEEN 50 and 60
or
select *
from ( SELECT *, ROW_NUMBER() OVER (ORDER BY Year) AS [Row] FROM table1) t1
where [Row] between 50 and 60
Question is why do you want to keep your derived column in quotes. If you have space in your column name or if it is a keyword, then use brackets.
The reason for your error message is, its trying to compare the hardcoded value 'Row' with 50 and 60. so you are getting conversion failure error as 'Row' is a character string and 50, 60 are numerical values.
Even though, you try to use CONVERT function, it won't work. reason is simple. Are you able to convert a hardcoded string 'Row' to a numerical value?
Hope you got the point.
0
 
Chris LuttrellSenior Database ArchitectCommented:
The cleanest way is with the CTE and as has been mentioned, you do not need the quotes or brackets around Row.  The below code worked in my test just fine.
;WITH CTE as (
SELECT *, ROW_NUMBER() OVER (ORDER BY Year) AS Row 
FROM table1 
)
SELECT * from CTE 
WHERE Row between 50  and 60

Open in new window

0
 
J_KoganAuthor Commented:
Thank you
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now