CODEWITHSUNDEEP
sql-serverdatabase
user1961514
user1961514

Reputation: 13

Order select using the the values of in(....) SQL server

Is there any way to order the result (for example)

SELECT id,age, Location
FROM Student 
WHERE Location in ('NY','DC','MI','TE')

instead of having them as

id     age     Location
1       2       DC
2       2       NY
4       2       MI
6       2       TE

The Result should be ordered as the order of in ('NY','DC','MI','TE'): 

id     age     Location
2       2       NY
1       2       DC
4       2       MI
6       2       TE

I figured that It's possible by having a temp table and inserting the ('NY','DC','MI','TE') into this table. Then using a left join will order it as wanted.

Is there a better solution.

Please Advise.

Upvotes: 0

Views: 67

Answers (3)

passingby
passingby

Reputation: 24

Assuming you don't have to select city codes from a table, try some of the following:

ORDER BY FIELD(Location,'NY','DC','MI','TE')
ORDER BY FIND_IN_SET(Location,'NY,DC,MI,TE')
ORDER BY LOCATE(CONCAT(',',Location,','),',NY,DC,MI,TE,')
ORDER BY Location IN ('NY','DC','MI','TE')

IN could also accept a sub-query (ORDER BY Location IN SELECT city_code FROM ...)

Upvotes: -1

Phil Sandler
Phil Sandler

Reputation: 28046

Any solution would be roughly the equivalent of using a temp table. You could use a CTE or a subquery, but you are essentially using different syntax to perform the same operation.

Upvotes: -1

C3roe
C3roe

Reputation: 96407

In MySQL this can be done using FIND_IN_SET, which gives back the index of the occurrence of the first argument in the second, the latter being a string containing comma-separated values.

I think the same thing should be possible using CHARINDEX - give this a try:

SELECT id, age, location
FROM student 
WHERE location IN ('NY','DC','MI','TE')
ORDER BY CHARINDEX (location, 'NY,DC,MI,TE')

Upvotes: 3

Related Questions