CODEWITHSUNDEEP
sqlsql-serversql-order-by
JustBeingHelpful
JustBeingHelpful

Reputation: 18990

ORDER BY not functioning in SELECT statement

I'm having difficulty with some SQL syntax within a stored procedure. I'm retrieving the most recent row from a SQL table (in SQL Server Management Studio), and using it later on in my stored procedure. That's why I'm setting variables (ie: @firstname, @lastname, etc..). I'm only interested in the most recent row based on the dbo.[ws_test_request].CREATED column value. I need the variables set based on the data queried, so I need to get the "This doesn't work" query working.

PS: the "2011051001" will be a variable... I'm just trying to get it to work.

This works:

SELECT 
    TOP 1
    @firstname = [sample_donor_firstname],
    @lastname = [sample_donor_lasttname],
    @middlename = [sample_donor_middlename]
FROM dbo.[ws_test_request]
WHERE 
    [sample_specimen_id] = '2011051001'
ORDER BY dbo.[ws_test_request].created DESC

This doesn't work:

SELECT 
    TOP 1
    [sample_donor_firstname],
    [sample_donor_lasttname],
    [sample_donor_middlename]
FROM dbo.[ws_test_request]
WHERE 
    [sample_specimen_id] = '2011051001'
ORDER BY dbo.[ws_test_request].created DESC

Thank you!!

Upvotes: 0

Views: 286

Answers (3)

JustBeingHelpful
JustBeingHelpful

Reputation: 18990

Hi guys, I must need glasses.

SELECT

TOP 1

@firstname = [sample_donor_firstname],

@lastname = [sample_donor_lasttname],

@middlename = [sample_donor_middlename]

FROM dbo.[ws_test_request]

WHERE

[client_id] = @clientid

AND [sample_specimen_id] = @sample_identifyingnumber; -- <============= BAD!!

ORDER BY dbo.[ws_test_request].created DESC

When I tested it with the "2011051001" value, I had set @sample_identifyingnumber to "2011051001" although I still had the semi-colon. That's what caused my ORDER BY clause to fail, and get the red underlines. There was nothing wrong with my code in my question. But thanks for the ideas of using cursors! I learned SQL in college, but haven't used it much in 5 years. So thanks for the ideas.

Upvotes: 0

M.R.
M.R.

Reputation: 4847

Your first query seems correct - the second query is not actually setting the variables, so you just need to put the variables in there...


DECLARE @donorid INT, 
@firstname NVARCHAR(255), 
@lastname NVARCHAR(255), 
@identifyingnumber NVARCHAR(255), -- SSN/MRN 
@middlename NVARCHAR(255), 
@comment NVARCHAR(1023


SELECT       
TOP 1      
@firstname = [sample_donor_firstname],      
@lastname = [sample_donor_lasttname],      
@middlename = [sample_donor_middlename]  
FROM dbo.[ws_test_request]  
WHERE       
[sample_specimen_id] = '2011051001'  
ORDER BY dbo.[ws_test_request].created DESC 

-- and then select the results (or use them wherever else you want to use them)
Select @firstname as FirstName, @lastname as LastName, @middlename as MiddleName

Upvotes: 0

CristiC
CristiC

Reputation: 22708

I don't see anything wrong with your second query. If you want to use the results on a later point you can use your first query or use declare a cursor:

DECLARE MyCursor CURSOR FOR
SELECT 
    TOP 1
    [sample_donor_firstname],
    [sample_donor_lasttname],
    [sample_donor_middlename]
FROM dbo.[ws_test_request]
WHERE 
    [sample_specimen_id] = '2011051001'
ORDER BY dbo.[ws_test_request].created DESC

and then open & fetch results:

OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @firstname, @lastname, @middlename

Upvotes: 1

Related Questions