disable order by in sql query

Question

select HId, TestM, *
from TestDb
where Hid in ( 4, 5, 7, 9, 1, 132, 312)

If I run the above .sql file the first column will be sorted ascending by default.

I do want the Hid column to be in order of the values inside the where clause. How can I achieve this?

Hid | TestM | ...
_______________
4

5

7

9

1

132

312

Answer 1

You have to use a CASE expression in the ORDER BY clause:

SELECT HId, TestM, *
FROM TestDb
WHEN Hid IN ( 4, 5, 7, 9, 1, 132, 312)
ORDER BY CASE Hid 
            WHEN 4 THEN 1
            WHEN 5 THEN 2
            WHEN 7 THEN 3
            WHEN 9 THEN 4
            WHEN 1 THEN 5
            WHEN 132 THEN 6
            WHEN 312 THEN 7
         END

Also note that tables are just unordered sets, so if you leave the ORDER BY clause out, then there is no guaranteed order for your result set.

Answer 2

SQL doesn't have implicit ordering. If no ORDER BY is stated then the results can come back in any order, and potentially different orders each time the same query is executed on the same data.

The often used approach is to use a CASE statement, but that can become clunky...

ORDER BY
    CASE HId WHEN   4 THEN 1
             WHEN   5 THEN 2
             WHEN   7 THEN 3
             WHEN   8 THEN 4
             WHEN   1 THEN 5
             WHEN 123 THEN 6
             WHEN 312 THEN 7 END

An alternative is to JOIN on a table that includes both the values you are filtering by, and a column to sort. It's slightly less clunky, more relational and a bit easier to expand...

SELECT
    TestDB.HId, TestDB.TestM, TestDB.*
FROM
    TestDB
INNER JOIN
(
              SELECT   4 AS val, 1 AS ordinal
    UNION ALL SELECT   5 AS val, 2 AS ordinal
    UNION ALL SELECT   7 AS val, 3 AS ordinal
    UNION ALL SELECT   8 AS val, 4 AS ordinal
    UNION ALL SELECT   1 AS val, 5 AS ordinal
    UNION ALL SELECT 132 AS val, 6 AS ordinal
    UNION ALL SELECT 312 AS val, 7 AS ordinal
)
    filter
        ON filter.value = TestDB.HId
ORDER BY
    filter.ordinal