CODEWITHSUNDEEP
mysql
Scorpioniz
Scorpioniz

Reputation: 1449

mysql need to get custom order of result

Have MySQL table like id, parent_id, order_nr

Now selecting everything like this 

SELECT * FROM `table1` WHERE 1 ORDER BY `id` DESC

And getting result like

`id`, `parent_id`, `order_nr`
"27",   "23",       "3" 
"26",   "23",       "2" 
"25",   "23",       "1" 
"24",   "0",        "0" 
"23",   "0",        "0" 
"22",   "0",        "0"

Need to get same result just in order like this: 

`id`, `parent_id`, `order_nr`
"24",   "0",        "0" 
"23",   "0",        "0" 
"25",   "23",       "3" 
"26",   "23",       "2" 
"27",   "23",       "1"
"22",   "0",        "0"

Upvotes: 0

Views: 54

Answers (5)

onedaywhen
onedaywhen

Reputation: 57023

When the parent_id is 0 then just use the id otherwise use the parent_id; sorting by order_nr then breaks ties. All sorting is descending order:

SELECT id, parent_id, order_nr,
       id AS sort_order
  FROM table1
 WHERE parent_id = '0'
UNION
SELECT id, parent_id, order_nr,
       parent_id AS sort_order
  FROM table1
 WHERE parent_id <> '0'
 ORDER
    BY sort_order DESC, order_nr DESC;

Upvotes: 0

Serg M Ten
Serg M Ten

Reputation: 5606

Assuming two digits numbers for ids (but can be extended to any number of digits)

SELECT IF(parent_id=0,id,parent_id)*100+IF(parent_id=0,0,id)),* FROM table ORDER BY 1 DESC

or if ids are strings

SELECT CONCAT(IF(parent_id='0',LPAD(id,2,'0'),LPAD(parent_id,2,'0')),IF(parent_id='0','00',LPAD(id,2,'0'))),* FROM table ORDER BY 1 DESC

This will give you a first column which is

2400 2300 2227 2226 2225 2200

Upvotes: 0

AddWeb Solution Pvt Ltd
AddWeb Solution Pvt Ltd

Reputation: 21681

SELECT * FROM `table1` WHERE 1 ORDER BY `parent_id`, `order_nr` ASC

Output would be like:

`id`, `parent_id`, `order_nr`
"24",   "0",        "0" 
"23",   "0",        "0" 
"22",   "0",        "0" 
"25",   "22",       "1" 
"26",   "22",       "2" 
"27",   "22",       "3"

Upvotes: 0

bogzy
bogzy

Reputation: 126

how about this one:

SELECT * FROM `table1` WHERE 1 ORDER BY order_nr,parent_id,id DESC

you can try to swap what column should be order first.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Assuming that parent_id and order_nr are varchar columns, but you want to sort them as numbers, then you can cast the columns when you order:

SELECT *
FROM table1
ORDER BY CAST(parent_id AS SIGNED),   -- no need for CAST if already numeric
         CAST(order_nr AS SIGNED)

Note: Your first desired output does not seem to have any evident logic behind, so I gave a solution for the second one.

Upvotes: 0

Related Questions