1,105,263 Community Members

How to store the ROW_number into the variable in SQL 2008?

Member Avatar
jacksantho
Junior Poster
169 posts since Feb 2011
Reputation Points: -3 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi,

Trying this Query,

[B]DECLARE @SR VARCHAR(8),@SC CHAR(15),@ST CHAR(15),@SNO int;
 SELECT * FROM (SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY a.rno) AS rowid,
@SR=a.rno,@SC=a.kat,
@ST=a.type FROM SMas a,
 SDet b WHERE 
 b.id='X' AND
 a.rno=b.rno) AS C WHERE C.rowid=4

[/B]

but getting this following error:

[B][I][U]Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='. (In this area,@SR=a.rno,@SC=a.kat,
@ST=a.type)
[/U][/I][/B]

Please help me out. Thanks in advance
[/I][/B]

Member Avatar
adam_k
Veteran Poster
1,056 posts since Jun 2011
Reputation Points: 239 [?]
Q&As Helped to Solve: 212 [?]
Skill Endorsements: 17 [?]
 
1
 

I don't have an SQL 2008 to test, but I'm guessing that your query fails for a number of reasons.
The 1st one is that you are assigning values to variables inside the nested query.
The 2nd one would be that distinct and row_number don't mix very well as row_number will make every record unique.

Have you tried:

DECLARE @SR VARCHAR(8),@SC CHAR(15),@ST CHAR(15),@SNO int;
select @SR=c.rno,@SC=c.kat,@ST=c.type, @SNO = c.rowid --I'm guessing @SNO is for rowid
from (SELECT ROW_NUMBER() OVER (ORDER BY a.rno) AS rowid,
a.rno,a.kat,a.type 
FROM SMas a, SDet b
WHERE  b.id='X' AND
 a.rno=b.rno ) c  
WHERE C.rowid=4
Member Avatar
BitBlt
Practically a Posting Shark
895 posts since Feb 2011
Reputation Points: 442 [?]
Q&As Helped to Solve: 163 [?]
Skill Endorsements: 16 [?]
Featured
 
1
 

I came up with nearly the same query as @adam_k, but he beat me to posting. Only difference between his and mine is that I included the DISTINCT clause. And I did test it with fake tables/data and it worked fine both ways.

Member Avatar
jacksantho
Junior Poster
169 posts since Feb 2011
Reputation Points: -3 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

HI,

Adam k and BitBit . Really Thanks for your guidance and for your help. It's too working for me.

Question Answered as of 2 Years Ago by BitBlt and adam_k
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article