Menu
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.
772
Summary. Use the ORDER BY clause to sort the result set by one or more columns. Use the ASC option to sort the result set in ascending order and the DESC option to sort the result set in descending order. The ORDER BY clause is evaluated after the FROM and SELECT clauses.
The order by the statement is used in SQL to sort the result set in ascending or descending by mentioning it in the suffix as DESC (for descending) and for ASC(for ascending).
select type , COUNT from TABLE order by FIELD(type,'A','B','C','D') ; It works fine if the column type has value for 'A,B,C,D' . In some cases the order by FIELD('A','B','C','D') some columns may not have value in table . In this cases I want to put 0 for it and construct a result .
The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
