MySQL: How to ORDER BY and SET ids?

mysql order by field
mysql order by alphabetical
mysql order by multiple columns
mysql order by case
mysql multiple order by
double order by mysql
order by 1 in mysql
nested order by mysql

I already googled but couldn't find a solution.

I have two columns called order_id and name. I want to ORDER BY name and then SET ascending order_id.

Like this:

order_id   name
1          Arya
2          Herbert
3          Paul
4          Peter
5          Tiffany

My id column is int(4) and by default value is 0. It's not PRIMARY or UNIQUE. (It'a also not the main id. Main id is of course PRIMARY.

How can I do this with SQL?


For versions < 8.0 you can use this:

select @rn := 0;

UPDATE tbl T1
JOIN (select @rn := @rn + 1 rn, `name` from tbl order by `name`) T2
  ON T1.`name` = T2.`name`
SET T1.order_id = T2.rn

Demo

Useful article related to your problem: MySQL UPDATE JOIN

How does ORDER BY FIELD() in MySQL work internally, For the record. SELECT * FROM mytable WHERE id IN (1,2,3,4) ORDER BY FIELD(id,3,2,1,4);. should work as well because you do not have to  You can also use the MySQL ORDER BY clause to sort by relative position in the result set, where the first field in the result set is 1. The next field is 2, and so on. For example: SELECT last_name, first_name, city FROM contacts WHERE last_name = 'Johnson' ORDER BY 3 DESC;


You could use ROW_NUMBER(MySQL 8.0+):

SELECT name, ROW_NUMBER() OVER(ORDER BY name) AS rn
FROM tab
ORDER BY rn;

Update:

UPDATE tab
SET order_id = (SELECT rn FROM (SELECT name,ROW_NUMBER() OVER(ORDER BY name) AS rn
                                FROM tab)s
                WHERE s.name = tab.name); -- assuming that name is UNIQUE

DBFidde Demo

MySQL ordering results by specific field values, In MySQL we can sort the results in ascending or descending order very easily by using the ORDER BY clause. However, there are times when you want to sort the results in a specific order which cannot be done using the ASC or DSC. FIELD() of MySQL ORDER BY clause can be used to sort the results in a specific order. Introduction to MySQL ORDER BY clause. When you use the SELECT statement to query data from a table, the result set is not sorted. It means that the rows in the result set can be in any order. To sort the result set, you add the ORDER BY clause to the SELECT statement.


As your mysql version is below 8.0 so you have to manually generate orderid below can help you

select  t.*, 
                   @rownum := @rownum + 1 AS order_id from 
            (
            select * from
             tab o order by name asc
            )  as t , (SELECT @rownum := 0) r

http://www.sqlfiddle.com/#!9/ae3fda/3

MySQL: ORDER BY Clause, This MySQL tutorial explains how to use the MySQL ORDER BY clause with syntax The MySQL ORDER BY clause is used to sort the records in your result set. MySQL Order By clause is used to sort the table data in either Ascending order or Descending order. By default, data is not inserted into Tables in any order unless you have an index. So, If you want to retrieve the data in any particular order, you have to sort it by using MySQL Order By statement.


MySQL 8.0 Reference Manual :: 8.2.1.16 ORDER BY , In some cases, MySQL may use an index to satisfy an ORDER BY clause and A filesort operation uses temporary disk files as necessary if the result set is too are pairs that contain the sort key value and row ID of the original table row. How can I have concatenated names ordered by their sequence in the set (it is a variable where I have ids comma separated like this "4,3,2")? So far the names are being ordered by id "2,3,4". This is my subquery.


MySQL 5.7 Reference Manual :: 13.2.11 UPDATE Statement, UPDATE t SET id = id + 1 ORDER BY id DESC;. You can also perform UPDATE operations covering multiple tables. However, you cannot use ORDER BY or  MySQL ORDER BY is used in conjunction with the SELECT query to sort data in an orderly manner. The order by clause is used to sort the query result sets in either ascending or descending order. The order by clause is used to sort the query result sets in either ascending or descending order.


PHP MySQL Select Data With ORDER BY Clause, First, we set up the SQL query that selects the id, firstname and lastname columns from the MyGuests table. The records will be ordered by the lastname column. You can create a temp table with two columns (ID, order_num): ID order_num 118 1 17 2 113 3 23 4 72 5 Then join: SELECT * from table INNER JOIN #temp_table ON table.id = #temp_table.id Notice that you can drop the IN clause.