How to sort a MySQL query by two columns
Below is my DB data, how can I sort it by sid and prev_sid using php / mysql !?
sid prev_sid type
000 197 app_home
197 198 page_teach
198 218 page_teach
199 211 page_step
211 207 link
218 559 page_step
559 199 page_step
Result:
sid prev_sid type
000 197 app_home
197 198 page_teach
198 218 page_teach
218 559 page_step
559 199 page_step
199 211 page_step
211 207 link
000 → 197 → 198 → 218 → 559 → 199 → 199 → 211 → 207
a source to share
I would suggest that you rethink your desk design. This presentation of data does not allow sorting rows conveniently and efficiently.
If you need to keep the current table design, I suspect that you will need to use SQL variables and that sorting choices are getting pretty messy and probably inefficient.
Another possibly better solution would be to do the sorting on the application side, after which it could be done easily in linear time using a hashmap by mapping the sid values to the prev_sid and type value pairs.
a source to share
It looks like your data is an adjacency list (i.e. sed and prev_sid define a recursive parent-child relationship). If so, then to get the elements in order, you can convert them to a nested set and sort by the left value of each node to order the elements. For more information on hierarchical data, see Managing Hierarchical Data in MySQL .
BTW, if you want to convert an adjacency list to a nested set using PHP check my answer in PHP Traversing mySQL Node tree .
a source to share
If you take a close look at your data, you will see that you only need to sort sid
. This is done in SQL by adding ORDER BY sid
to your query.
SELECT sid, prev_sid, type FROM table ORDER BY sid
If you need to sort by two columns (which is not the case here, but might be useful) ORDER BY
can take a list of columns as a parameter.
SELECT sid, prev_sid, type FROM table ORDER BY sid, prev_sid
a source to share
218> 559> 199
Do not use any numbering system that I have ever used. I don't think you have explained your problem very well.
Looking at the example, I suspect you want to sort based on either the sid value or the value for perv_sid, whichever is less, so:
ORDER BY LEAST (sid, prev_sid)
FROM.
a source to share