For example I have in the table EMPLOYEE:
(code, name)
(1, 'Jimmy')
(2, 'Albert')
(3, 'Michelle')
(4, 'Felix' )
if you do: (select * from EMPLOYEE) you will get:
(1, 'Jimmy')
(2, 'Albert')
(3, 'Michelle')
(4, 'Felix' )
if you do: (select * from EMPLOYEE where code in (1,3,2,4) you will get:
(1, 'Jimmy')
(2, 'Albert')
(3, 'Michelle')
(4, 'Felix' )
How to get it in the order of CSV values in the IN clause, as is?
(1, 'Jimmy')
(3, 'Michelle')
(2, 'Albert')
(4, 'Felix' )
The MySQL ORDER BY Keyword 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.
An "ALTER TABLE ORDER BY" statement exist in the syntaxes accepted by MySQL. According to the documentation, this syntax: - only accept *one* column, as in "ALTER TABLE t ORDER BY col;" - is used to reorder physically the rows in a table, for optimizations.
Answer: ORDER BY is a clause that is typically used along with SELECT queries in MySQL and is used to return the result set sorted in the given order against the given column or field in the table.
To select first 10 elements from a database using SQL ORDER BY clause with LIMIT 10. Insert some records in the table using insert command. Display all records from the table using select statement.
Use the FIND_IN_SET function:
SELECT e.*
FROM EMPLOYEE e
WHERE e.code in (1,3,2,4)
ORDER BY FIND_IN_SET(e.code, '1,3,2,4')
Or use a CASE statement:
SELECT e.*
FROM EMPLOYEE e
WHERE e.code in (1,3,2,4)
ORDER BY CASE e.code
WHEN 1 THEN 1
WHEN 3 THEN 2
WHEN 2 THEN 3
WHEN 4 THEN 4
END
The general solution to this problem, retaining the order based on your input (CSV) file, is to add an AUTO_INCREMENT
column to your table and order based on that. You probably will never display it as part of your query, but you can order on it to get the original order in your input file, after the import.
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