SQL Where in (...) Sort by Order of the List

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 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

How to sort a list of lists using a sql query?

If I understand correctly, you can sort the lists by the dates concatenated together:

select ld.*
from list_date ld join
     (select list_id, group_concat(date) as dates
      from ld
      group by list_id
     ) ldc
     on ld.list_id = ldc.list_id
order by ldc.dates, ld.date;

Sort results based on order of entries in the WHERE's IN clause

 select qtable.* 
 from qtable join (values (15),(55),(2),(3),(4))a(id)
 on a.Id = qtable.qID

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

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.

SQL Order By list of strings?

Try using this:

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

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.

How to SORT in order as entered in SQL Server?

Here is an in-line approach

Example

Declare @List varchar(max)='212345, 312345, 145687, 658975, 256987, 365874, 568974, 124578, 125689'

Select A.AccountNumber 
      ,A.EndDate
 From  Accounts A
 Join (
        Select RetSeq = Row_Number() over (Order By (Select null))
              ,RetVal = v.value('(./text())[1]', 'int')
        From  (values (convert(xml,'<x>' + replace(@List,',','</x><x>')+'</x>'))) x(n)
        Cross Apply n.nodes('x') node(v)
      ) B on A.AccountNumber = B.RetVal
 Order By B.RetSeq

EDIT - the subquery Returns

RetSeq  RetVal
1       212345
2       312345
3       145687
4       658975
5       256987
6       365874
7       568974
8       124578
9       125689

---

Related Topics