MySQL: How to ORDER BY and SET ids?
I already googled but couldn't find a solution.
I have two columns called
name. I want to
ORDER BY name and then
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
UNIQUE. (It'a also not the main id. Main id is of course
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
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
SELECT name, ROW_NUMBER() OVER(ORDER BY name) AS rn FROM tab ORDER BY rn;
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
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
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 :: 184.108.40.206 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.