How to sort MySQL query by two columns

Question

The following is my DB data, how can I sorting it by sid and prev_sid with 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

Answer 1

I would suggest that you reconsider your table design. This type of data representation prevents the rows from being sorted conveniently and efficiently.

If you need to keep the current table design, I suspect that you will need to involve SQL variables, and that the sorting select becomes quite messy and probably not efficient.

Another, possibly better solution, would be to do the sorting on the application side, at which point it could easily be done in linear time using a hash-map, mapping sid-values to pairs of prev_sid and type values.

Answer 2

If you look carefully at your data, you will see that you only need to sort by sid. This is done in SQL by appending ORDER BY sid to your query.

SELECT sid, prev_sid, type FROM table ORDER BY sid

If you need to sort on two columns (which is not the case here but it might be useful), ORDER BY can take a list of columns as parameter.

SELECT sid, prev_sid, type FROM table ORDER BY sid, prev_sid

Answer 3

218 > 559 > 199

Not using any numbering system I've ever used it's not. I don't think you've explained your problem very well.

Looking at the example I suspect you want to sort based on either the value for sid or the value for perv_sid, depending on which is the lesser, so:

ORDER BY LEAST(sid, prev_sid)

C.

Answer 4

It looks like your data is an adjacency list (ie sed and prev_sid define a recursive parent-child relationship). If this is the case, then to get the items in order you could convert it to a nested set and sort by the left value of every node to get the items in order. For more information on hierarchical data refer to http://dev.mysql.com/tech-resources/articles/hierarchical-data.html.

BTW If you want to convert an adjacency list to a nested set using PHP check out my answer at http://stackoverflow.com/questions/2432725/php-moving-mysql-tree-node/2433013#2433013.