SQL - Order by List Order

SQL Order By list of strings?

Try using this:

select * from table 
order by FIELD(Code, 'Health', 'Phone', 'Freeze', 'Hot')

SQL - order by list order

If you need the output to appear in a particular order, then you need to specify that order, using something the server can sort. Not knowing which engine you're working against, the general scheme would be to create a temp table or use rowset constructors to pair each record ID with its desired sort order.

E.g. (SQL Server)

declare @T table (RecordID int,Position int)
insert into @T (RecordID,Position)
select 22,1 union all
select 15,2 union all
select 105,3 union all
select 1,4 union all
select 65,5 union all
select 32,6

select * from Table t inner join @T t2 on t.RecordID = t2.RecordID order by t2.Position

SQL ORDER BY - how to honour same order as list in WHEN clause

On SQL Server, you may order using a CASE expression:

SELECT *
FROM customers
WHERE customer_id in ('29383', '49405', '47483', '10209','46383', '93838')
ORDER BY CASE customer_id
         WHEN '29383' THEN 1
         WHEN '49405' THEN 2
         WHEN '47483' THEN 3
         WHEN '10209' THEN 4
         WHEN '46383' THEN 5
         WHEN '93838' THEN 6 END;

Note that if you have a persistent need for this ordering, it might make more sense to keep this customer_id values and their mappings/orderings in a separate table. Then, join to this table and use the ordering value in your ORDER BY clause.

SQL WHERE IN (...) sort by order of the list?

Select ID list using subquery and join with it:

select t1.*
from t1
inner join
(
  select 1 as id, 1 as num
  union all select 5, 2
  union all select 3, 3
) ids on t1.id = ids.id
order by ids.num

UPD: Code fixed

Ordering by the order of values in a SQL IN() clause

Use MySQL's FIELD() function:

SELECT name, description, ...
FROM ...
WHERE id IN([ids, any order])
ORDER BY FIELD(id, [ids in order])

FIELD() will return the index of the first parameter that is equal to the first parameter (other than the first parameter itself).

FIELD('a', 'a', 'b', 'c')

will return 1

FIELD('a', 'c', 'b', 'a')

will return 3

This will do exactly what you want if you paste the ids into the IN() clause and the FIELD() function in the same order.

ORDER BY the IN value list

You can do it quite easily with (introduced in PostgreSQL 8.2) VALUES (), ().

Syntax will be like this:

select c.*
from comments c
join (
  values
    (1,1),
    (3,2),
    (2,3),
    (4,4)
) as x (id, ordering) on c.id = x.id
order by x.ordering

sql ORDER BY multiple values in specific order?

...
WHERE
   x_field IN ('f', 'p', 'i', 'a') ...
ORDER BY
   CASE x_field
      WHEN 'f' THEN 1
      WHEN 'p' THEN 2
      WHEN 'i' THEN 3
      WHEN 'a' THEN 4
      ELSE 5 --needed only is no IN clause above. eg when = 'b'
   END, id

Order items in MySQL by a fixed list?

You can do that by using either:

ORDER BY FIND_IN_SET(id, '3,11,7,1')

or

ORDER BY FIELD(id, 3, 11, 7, 1)

or

ORDER BY CASE id WHEN 3 THEN 0
                WHEN 11 THEN 1
                 WHEN 7 THEN 2
                 WHEN 1 THEN 3
                        ELSE 4
         END

SQL - How to ORDER BY different columns in one query

You have two basic options: you can order in the db or you can order in the front end. If you are paging your data, you want to do it in the db but if it is only a few rows, you could do it either place. Remember, the front end cannot sort rows you did not pull from the database.

To sort in the front end, please consult the documentation for the programming frameworks you are using.

To sort in the back-end, you need to send a new db query every time you want a different sort. This makes sense since you may not have all the rows retrieved you want to display. So from your question you understand the SQL, so you just need to send it on each click. Again, consult the documentation of the programming frameworks you are using for help in making that happen.

---

Related Topics