CODEWITHSUNDEEP
sqlselect
RossW
RossW

Reputation: 149

SQL select rows in a given Order

I have a table that i wish to select rows from, I want to be able to define the order in which the results are defined. 

SELECT * 
FROM Table 
ORDER BY (CSV of Primary key)

EG: 

Select * 
from table 
order by id   (3,5,8,9,3,5)

Wanted results 

ID | * |
-----------
3 ....
4 ....

etc

I'm guessing this isn't possible?

Upvotes: 0

Views: 2080

Answers (4)

IMSoP
IMSoP

Reputation: 97718

For a short list of values, it's possible to generate a CASE statement which prioritises the options. For instance, to order by id in sequence (3,5,8,9):

ORDER BY CASE id
    WHEN 3 THEN 0
    WHEN 5 THEN 1
    WHEN 8 THEN 2
    WHEN 9 THEN 3
    ELSE 4
END ASC

This obviously doesn't scale that well for longer lists, and in your example you have duplicate items (not sure if that was deliberate) which requires you to select the same row twice in different points in the sequence.

A more complex solution is to create a table of (id, sequence_position) pairs, and JOIN that to the rest of your query. A normal INNER JOIN would also filter by those rows; if that wasn't what was wanted, you could use LEFT JOIN and COALESCE(sequence_position, $num_items+1) to give the same as the ELSE clause in the CASE version.

You don't mention what DBMS (MySQL, Postgres, etc) and version you're running, so I can't say exactly what your options for that are, but they include things like temporary tables, and CTEs (WITH statements) or sub-queries involving VALUES clauses or UNION ALL statements.

Note that one way or another you're probably looking at dynamically generating SQL here, not just using a comma-separated string directly. It's possible that you could make a custom function to generate that SQL directly, or even that your DBMS has set-based functions that would help, but again I don't know what you're using.

Upvotes: 0

magma
magma

Reputation: 11

Try to use a CASE expression in the ORDER BY clause SELECT * FROM something

ORDER BY CASE #something WHEN 'this' THEN 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269713

You can use the field() function in MySQL:

order by field(id, 3, 5, 8, 9, 3, 5)

Of course, 3 and 5 match the the first time, not the second, so they are superfluous.

You can use a similar function choose() in SQL Server. Or, use case in any database:

order by (case when id = 3 then 1
               when id = 5 then 2
               when id = 8 then 3
               when id = 9 then 4
          end);

Upvotes: 2

againstdemons84
againstdemons84

Reputation: 16

If I'm understanding your question correctly you could do something like this:

Select * 
from table 
order by CASE id  WHEN 3 THEN 1 WHEN 5 THEN 2 WHEN 8 THEN 3 WHEN 9 THEN 4 WHEN 3 THEN 5 WHEN 5 THEN 6 ELSE 10000 END

Upvotes: 0

Related Questions