MSSQL : Order By specific column in specific order

Databases
1

Hello

I am stuck with a problem. I want a MSSQL equivalent for similar MySQL query:

$IDS are in array converted to string to pass in the query: (Below if not a part of code, just example text to show you what I want to do)

$IDS = “‘B’, ‘A’, ‘D’, ‘E’, ‘C’”

SELECT id, name, some_date
FROM mytable
WHERE name IN ($IDS)
ORDER BY FIELD(name, $IDS), some_date DESC

Please guide me !
Thank you
ZH

2

CASE expressions, using SUBSTRING

note: will work only if each term in the $IDS list is the same length, and if there is a fixed number of them – otherwise, you’ll have to consider designing a new “sequence” table containing the $IDS terms each in its own row, along with a sequence number

3

:frowning: I am afraid that length of the terms in $IDS is same however, there can be 1 OR more terms.

4

okay, so whatever language you are using to generate the query and insert the $IDS string, you will have to use that language to generate the CASE expressions

5

Actually I am working on JasperStudio / Jasper Report. So query there is just text.

6

Can you give me an example of the case statement, may be you can take the my example query and give me an idea like what the case statement should look like ?

7

SELECT id , name , some_date FROM mytable WHERE name IN ( 'Curly' , 'Larry' , 'Moe' , 'Shemp' , 'Joe' , 'Curly Joe' ) ORDER BY CASE WHEN name = 'Curly' THEN 1 WHEN name = 'Larry' THEN 2 WHEN name = 'Moe' THEN 3 WHEN name = 'Shemp' THEN 4 WHEN name = 'Joe' THEN 5 WHEN name = 'Curly Joe' THEN 6 ELSE 9 END , some_date DESC

8

So nice of you !

May you get more success, fame and knowledge ! May you keep helping others.

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.