Select a row from a select used to set variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select a row from a select used to set variables

Author

Topic

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2013-05-22 : 17:40:55

Hi

I need to select a specific row from a table variable and came across the following code:


SELECT * FROM
    (SELECT ROW_NUMBER() 
        OVER (ORDER BY Salary) AS Row, 
        EmployeeId, EmployeeName, Salary 
    FROM Employees) AS EMP
WHERE Row = 4

The problem I'm having is that I also want to use the SELECT to set variables (not return a table rwo). When I replace the usual table column names 'EmployeeId, EmployeeName, Salary' with '@temp_classID = classID, @temp_locationnID = ClassLocationID' it doesn't work?

Any ideas?

Thanks as always

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-22 : 17:49:07

It should work. Can you post the statement that does not work? You have to do the assignment in the outer query, not in the inner query.

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2013-05-29 : 05:47:58

Try like this!

declare
@rownum int,
@id int,
@name datetime,
@sal int

SELECT @rownum=row,@id=EmployeeId,@name=EmployeeName,@sal=Salary
FROM
(SELECT ROW_NUMBER()
OVER (ORDER BY Salary) AS Row,
EmployeeId, EmployeeName, Salary
FROM Employees) AS EMP
WHERE Row = 4

SELECT @rownum,@id,@name,@sal

Thanks..

M.MURALI kRISHNA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-29 : 06:05:39

quote:
Originally posted by richardlaw

Hi

I need to select a specific row from a table variable and came across the following code:

SELECT * FROM
(SELECT ROW_NUMBER()
OVER (ORDER BY Salary) AS Row,
EmployeeId, EmployeeName, Salary
FROM Employees) AS EMP
WHERE Row = 4

The problem I'm having is that I also want to use the SELECT to set variables (not return a table rwo). When I replace the usual table column names 'EmployeeId, EmployeeName, Salary' with '@temp_classID = classID, @temp_locationnID = ClassLocationID' it doesn't work?

Any ideas?

Thanks as always

Assuming classID,ClassLocationID are columns available in Employees table you can use this


SELECT @temp_classID = classID, 
@temp_locationnID = ClassLocationID  
FROM
    (SELECT ROW_NUMBER() 
        OVER (ORDER BY Salary) AS Row, 
        classID,ClassLocationID 
    FROM Employees) AS EMP
WHERE Row = 4

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources