![]() |
MySQL question: how do you correctly sort by 2 columns?
I'm trying to sort the order by 2 INT columns but it doesn't seem to recognize the 2nd sort.
Everything in Google says to do it like this: Quote:
http://i.imgur.com/w0qss.jpg |
Quote:
It appears that id is unique in your database so there will never be more than one instance of id in the list, so what you actually want to do is just sort by views desc to put it into the proper order. Select.................order by views desc, id (you do not have to specify asc on id it will default to asc) That should produce: 5....1 12...1 4...0 6...0 ............ |
Quote:
Id is your primary key... just order by views desc, id; |
Well for my pagination to still work I need to sort by 'id DESC' first, so that the front page will show the latest 14 ID's, and the LIMIT will control which 14 results are shown on each numbered page.
It just doesn't seem to be using the 2nd sort either way I try it. If I do 'SORT BY views DESC, id' it will just sort the entire table by views, does the same thing as 'SORT BY views DESC', the id part is pretty much unneeded and goes unused for some reason. I think I will just have to grab the 14 results I want and then sort them by views afterwards, but I was hoping I could do all that in 1 query :/ |
Quote:
Editting in here, I had missed the last line so my answer did not make sense anymore ;p |
Quote:
|
you are sorting by date? then on each page you display it ordered by views?
|
Quote:
I've got it working how I want now, I just queried the latest 14 posts, stuck them in a multidimensional array and sorted that by views. Next page will grab the next 14 and sort them by views, etc.. Still don't understand why using 2 ORDER's in a query isn't working.. |
Quote:
The order by clause on your query is saying to sort the entire table by the order by clause and then give you the 14 you want. You dont want the entire table sorted by views, you just want the 14 that will be on your current page sorted by views. To do it in one query (which is actually 2) and get the results you want would be: select * from (select * from tablename order by id desc limit X,14) order by views desc |
Quote:
#1248 - Every derived table must have its own alias I think sub select is meant to be used with multiple tables? Idk I've never used em.. Edit: oh I needed to place it into an alias like select * from (select * from tablename order by id desc limit X,14) as aliasname order by views desc Does the trick thanks dude! |
Quote:
|
Quote:
|
All times are GMT -7. The time now is 02:58 PM. |
Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2025, vBulletin Solutions, Inc.
©2000-, AI Media Network Inc123