MySQL sort by some list

Viewed 60 times

I have a list of numbers: 7,1,3,2,123,55 (which are the ids of existing records)

I have a mysql table with the colums id and name, where id is an integer primary key. I want to select records from this table, but in a specific order, for example 7,1,3,2,123,55.

Is it possible to do this in MyISAM within query, without any post processing?
What is the simplest way to do this?

Answers

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

Friday, September 30, 2022

ashkar

I would say just build it yourself. You can set it up like this:

$query = "INSERT INTO x (a,b,c) VALUES ";
foreach ($arr as $item) {
  $query .= "('".$item[0]."','".$item[1]."','".$item[2]."'),";
}
$query = rtrim($query,",");//remove the extra comma
//execute query

Don't forget to escape quotes if it's necessary.

Also, be careful that there's not too much data being sent at once. You may have to execute it in chunks instead of all at once.

Saturday, November 5, 2022

klas_lindbäck

use FIELD

SELECT * 
FROM products 
order by FIELD(id,59,47,28,29,20), id desc 
limit 50

Ordering by specific field values with MySQL

Saturday, September 24, 2022

hans_passant

The function you're looking for is find_in_set:

 select * from ... where find_in_set($word, pets)

for multi-word queries you'll need to test each word and AND (or OR) the tests:

  where find_in_set($word1, pets) AND find_in_set($word2, pets) etc

Wednesday, August 17, 2022

thecontstruct

Do you mean:

SELECT id, Season, Episode 
FROM table 
ORDER BY Season ASC, Epsisode ASC

Sorting by multiple columns is as simple as it gets.

Wednesday, October 5, 2022

skn2093

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

mysql

MySQL sort by some list

Answers

php,mysql