MySQL question: how do you correctly sort by 2 columns? - GoFuckYourself.com

Jakez · 02-17-2011, 10:43 PM

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:

SELECT id,name,views FROM posts ORDER BY id ASC, views DESC LIMIT 0,14

But that gives me this, which is only sorting the ID's and not the views also:

sarettah · 02-17-2011, 11:46 PM

Quote:

Originally Posted by Jakez

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:

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

bbobby86 · 02-18-2011, 02:18 AM

Quote:

Originally Posted by sarettah

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.

...
Id is your primary key... just order by views desc, id;

Jakez · 02-18-2011, 06:58 PM

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 :/

sarettah · 02-18-2011, 07:15 PM

Quote:

Originally Posted by Jakez

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

sarettah · 02-18-2011, 07:23 PM

Quote:

Originally Posted by Jakez

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 :/

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.

woj · 02-18-2011, 08:23 PM

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

Jakez · 02-18-2011, 08:30 PM

Quote:

Originally Posted by woj

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

sarettah · 02-18-2011, 09:44 PM

Quote:

Originally Posted by Jakez

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

Jakez · 02-18-2011, 09:49 PM

Quote:

Originally Posted by sarettah

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!

ProG · 02-18-2011, 09:57 PM

Quote:

Originally Posted by Jakez

so that the front page will show the latest 14 ID's

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?

Jakez · 02-18-2011, 10:24 PM

Quote:

Originally Posted by ProG

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.

02-18-2011, 06:58 PM	#4
Jakez Confirmed User Industry Role: Join Date: Jan 2004 Location: oddfuturewolfgangkillthemall!!!!!!! Posts: 5,656	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 :/ __________________ [email protected] - jakezdumb - 573689400 Killuminati Last edited by Jakez; 02-18-2011 at 07:01 PM..

02-18-2011, 08:23 PM	#7
woj <&(©¿©)&> Industry Role: Join Date: Jul 2002 Location: Chicago Posts: 47,882	you are sorting by date? then on each page you display it ordered by views? __________________ Custom Software Development, email: woj#at#wojfun#.#com to discuss details or skype: wojl2000 or gchat: wojfun or telegram: wojl2000 Affiliate program tools: Hosted Galleries Manager Banner Manager Video Manager Wordpress Affiliate Plugin Pic/Movie of the Day Fansign Generator Zip Manager