CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user2142260
user2142260

Reputation: 31

sql server 2008 order by issue

I am trying to order by columns from another table, heres an example of the error i am getting

DECLARE @UserID1 varchar(31), @UserID2 varchar(31), @UserID3 varchar(31), @UserID4 varchar(31)

--getting data from table
SELECT  @UserID1 = ID1, @UserID2 = ID2, @UserID3 = ID3, @UserID4 = ID4
FROM USER_INFO
WHERE idref = @id

--select some data from another table with the values we got from USER_INFO table
SELECT age, name, location
FROM USER_DATA
WHERE NameId in(@UserID1, @UserID2, @UserID3, @UserID4)
ORDER BY @UserID1, @UserID2, @UserID3, @UserID4 -- this errors

The returned error is

The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

I am trying to order by ID1, ID2, ID3, ID4 FROM USER_INFO in ascending order

Upvotes: 2

Views: 69

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

SELECT age, name, location
FROM USER_DATA UD
INNER JOIN USER_INFO UI 
ON (UD.NameId=UI.ID1 OR UD.NameId=UI.ID2 OR UD.NameId=UI.ID3 OR UD.NameId=UI.ID4)
WHERE UI.idref = @id
AND NameId in(ID1,  ID2,  ID3,  ID4)
ORDER BY ID1,  ID2,  ID3,  ID4

OR

SELECT age, name, location
FROM USER_DATA UD
, USER_INFO UI 

WHERE UI.idref = @id
AND NameId in(ID1,  ID2,  ID3,  ID4)
ORDER BY ID1,  ID2,  ID3,  ID4

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726579

You can do it in this way:

SELECT age, name, location
FROM USER_DATA
WHERE NameId in(@UserID1, @UserID2, @UserID3, @UserID4)
ORDER BY CASE NameId
    WHEN @UserID1 THEN 1
    WHEN @UserID2 THEN 2
    WHEN @UserID3 THEN 3
    WHEN @UserID4 THEN 4
END

This will put records with NameId matching @UserID1 first, then records with NameId matching @UserID2, then @UserID3, and so on.

Upvotes: 2

Related Questions