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:
But that gives me this, which is only sorting the ID's and not the views also:
http://i.imgur.com/w0qss.jpg

Since Id is the first sort parameter it sorts by that first, then within id it sorts by views.

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
............

Yes. Totally right, if you want to do that. :thumbsup...
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 :/

Editting in here, I had missed the last line so my answer did not make sense anymore ;p

You could do it using a subselect. I think other than that you either just need to sort the results into view desc after you grab the dataset or you need to keep track of last id throughout your pagination scheme.

you are sorting by date? then on each page you display it ordered by views?

Nah sorting by ID (which pretty much goes in order just like the dates) because I need exactly 14 posts on each page regardless of how many were posted that day.

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..

You can do it in a sub select but you can't do it the way you are trying to do it.

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

I tried a sub select earlier, it gave me this error earlier and gives me the same error trying this one.

#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!

Then you shouldn't be using the primary ID to sort. If you delete a record, the deleted ID will be filled, and you will not get the "latest" ID. Perhaps you should add a field for "ON UPDATE CURRENT_TIMESTAMP" and sort by that instead?

ID is auto incremented. If it worked like that (filled in deleted ID's) it would be a nightmare on everything I've ever done lol.