MySQL Sort by Some List

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

MySQL sort by some list

Since 1 < 3 < 77 < 123, a simple ORDER BY id would suffice.

If, however, you want to order this way: 77, 3, 123, 1, then you could use function FIELD():

SELECT id, name
FROM mytable 
WHERE id IN (77, 3, 123, 1) 
ORDER BY FIELD(id, 77, 3, 123, 1)

If your query matches more rows than you list in FIELD

FIELD returns 0 when a row does not match any of the ids you list, i.e. a number smaller than the numbers returned for listed ids. This means, if your query matches more rows than the ones you list, those rows will appear first. For example:

SELECT id, name
FROM mytable 
WHERE id IN (77, 3, 123, 1, 400) 
ORDER BY FIELD(id, 77, 3, 123, 1)

In this example, the row with ID 400 will appear first. If you want those rows to appear last, simply reverse the list of IDs and add DESC:

SELECT id, name
FROM mytable 
WHERE id IN (77, 3, 123, 1, 400) 
ORDER BY FIELD(id, 1, 123, 3, 77) DESC

MYSQL ORDER BY FIELD from a list from and other table

A basic join should suffice:

select t2.*
from table2 t2 join
     table1 t1
     on t2.user_id = t1.id
order by t1.order_list;

Or, if you prefer, put a subquery in the order by:

select t2.*
from table2 t2
order by (select t1.order_list from table1 t1 where t1.id = t2.user_id);

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;

MySQL order by field() with variable list

Just add:

ORDER BY FIND_IN_SET(t.id, idlist)

Don't use field() for this; use find_in_set().

MySql: order lists by number of items and then by content

Like this, if you want 5 rows out of your fiddle:

select * from 

  --the main data
  grocery_lists gl

  --joined with
  inner join

  --the count of items in each list
  (
    select list_id, group_concat(item order by item_index asc) as grouped_items, count(*) as total_count 
    from grocery_lists gl
    group by list_id
  ) ct
  on gl.list_id = ct.list_id

--ordered by the count of items, then the index 
order by ct.total_count, ct.grouped_items, gl.item_index

You thus get rows out like:

   2, orange, 0  --sorts first because count - 1
   1, apple, 0   --sorts ahead of list 0 because "apple, banana" < "apple, bread"
   1, banana, 1
   0, apple, 0
   0, bread, 1

If list items are ints (and you want 5 rows)

I think you'll need to do this:

select * from 

  --the main data
  grocery_lists gl

  --joined with
  inner join

  --the count of items in each list
  (
    select list_id, group_concat(LPAD(item, 10, '0') order by item_index asc) as grouped_items, count(*) as total_count 
    from grocery_lists gl
    group by list_id
  ) ct
  on gl.list_id = ct.list_id

--ordered by the count of items, then by padded aggregate ints, then index 
order by ct.total_count, ct.grouped_items, gl.item_index

If your items are ints, padding them out to eg 10 wide with 0 makes the sort work because "0000000123, 00000000124" < "0000000123, 0000000125"

I picked 10 wide because int max is 4.5 billion; 10 digits. if your ints will be smaller, you can pad less

If youre comparing booleans, a similar strategy, maybe convert them to INTs (true=0, false=1 ?) so they sort ocrrectly, even when aggregated into a string..

If a list of T,T,F sorts ahead of T,F,F, then make T=0 and F=1.. for example

If you want 3 rows out of your fiddle..

Borrowed from Shadow, and adjusted for item being an int:

select list_id, group_concat(item order by item_index asc) as items, count(*) as list_length
from yourtable
group by list_id
order by list_length asc, group_concat(LPAD(item, 8, '0') order by item_index asc) asc

MySql order by specific ID values

You can use ORDER BY and FIELD function.
See http://lists.mysql.com/mysql/209784

SELECT * FROM table ORDER BY FIELD(ID,1,5,4,3)

It uses Field() function, Which "Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found" according to the documentation. So actually you sort the result set by the return value of this function which is the index of the field value in the given set.

MySQL Order by Amount of same Category not working when Amount the same

In MySQL 8+, window functions are the best way to write this. I want to point out that in earlier versions, you can use a subquery:

SELECT mt.id, mt.category 
FROM table mt 
ORDER BY (SELECT COUNT(*) FROM table mt2 WHERE mt2.category = mt.category),
         mt2.catgory, id;

With an index on mt(category) this might even be more efficient than the aggregation method.

MySQL: Order by specific items first then by time

FIELD() is tricky for this, because it returns 0 if there are no matches. You can construct an expression that does what you want:

order by coalesce(nullif(field(id, 6, 3, 2), 0), 999999),
         created_at desc

If you know that the ids are always descending for the fixed values, then you can use:

order by (case when id in (6, 3, 2) then id end) desc,
         created_at desc

MYSQL - How to sort column with ORDER BY and LIMIT and then sort those results by another column

Is this what you want?

SELECT t.*
FROM (SELECT t2.*, ABS(t1.v1-t2.v1)+ABS(t1.v2-t2.v2)+ABS(t1.v3-t2.v3) AS diff
      FROM tbl t1 JOIN
           tbl t2
           ON t1.id <> t2.id and t1.id = 1
      ORDER BY t2.id
      LIMIT 10
     ) t
ORDER BY diff;

You have position in your queries. I don't know what that is. Also, the subquery for t1 is unnecessary. You can just include the id in the on clause.

---

Related Topics