Welcome to the GoFuckYourself.com - Adult Webmaster Forum forums.

You are currently viewing our boards as a guest which gives you limited access to view most discussions and access our other features. By joining our free community you will have access to post topics, communicate privately with other members (PM), respond to polls, upload content and access many other special features. Registration is fast, simple and absolutely free so please, join our community today!

If you have any problems with the registration process or your account login, please contact us.

Post New Thread Reply

Register GFY Rules Calendar
Go Back   GoFuckYourself.com - Adult Webmaster Forum > >
Discuss what's fucking going on, and which programs are best and worst. One-time "program" announcements from "established" webmasters are allowed.

 
Thread Tools
Old 02-17-2011, 10:43 PM   #1
Jakez
Confirmed User
 
Jakez's Avatar
 
Industry Role:
Join Date: Jan 2004
Location: oddfuturewolfgangkillthemall!!!!!!!
Posts: 5,656
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:
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:
__________________
[email protected] - jakezdumb - 573689400

Killuminati
Jakez is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 02-17-2011, 11:46 PM   #2
sarettah
see you later, I'm gone
 
Industry Role:
Join Date: Oct 2002
Posts: 14,065
Quote:
Originally Posted by Jakez View Post
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
............
__________________
All cookies cleared!

Last edited by sarettah; 02-17-2011 at 11:49 PM..
sarettah is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 02-18-2011, 02:18 AM   #3
bbobby86
partners.sexier.com
 
bbobby86's Avatar
 
Industry Role:
Join Date: Jan 2007
Location: San Francisco, CA
Posts: 11,926
Quote:
Originally Posted by sarettah View Post
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;
__________________

bbobby86 is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 02-18-2011, 06:58 PM   #4
Jakez
Confirmed User
 
Jakez's Avatar
 
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..
Jakez is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 02-18-2011, 07:15 PM   #5
sarettah
see you later, I'm gone
 
Industry Role:
Join Date: Oct 2002
Posts: 14,065
Quote:
Originally Posted by Jakez View Post
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
__________________
All cookies cleared!

Last edited by sarettah; 02-18-2011 at 07:17 PM..
sarettah is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 02-18-2011, 07:23 PM   #6
sarettah
see you later, I'm gone
 
Industry Role:
Join Date: Oct 2002
Posts: 14,065
Quote:
Originally Posted by Jakez View Post
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.
__________________
All cookies cleared!
sarettah is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 02-18-2011, 08:23 PM   #7
woj
<&(©¿©)&>
 
woj's Avatar
 
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
woj is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 02-18-2011, 08:30 PM   #8
Jakez
Confirmed User
 
Jakez's Avatar
 
Industry Role:
Join Date: Jan 2004
Location: oddfuturewolfgangkillthemall!!!!!!!
Posts: 5,656
Quote:
Originally Posted by woj View Post
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..
__________________
[email protected] - jakezdumb - 573689400

Killuminati

Last edited by Jakez; 02-18-2011 at 08:32 PM..
Jakez is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 02-18-2011, 09:44 PM   #9
sarettah
see you later, I'm gone
 
Industry Role:
Join Date: Oct 2002
Posts: 14,065
Quote:
Originally Posted by Jakez View Post
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
__________________
All cookies cleared!

Last edited by sarettah; 02-18-2011 at 09:46 PM..
sarettah is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 02-18-2011, 09:49 PM   #10
Jakez
Confirmed User
 
Jakez's Avatar
 
Industry Role:
Join Date: Jan 2004
Location: oddfuturewolfgangkillthemall!!!!!!!
Posts: 5,656
Quote:
Originally Posted by sarettah View Post
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!
__________________
[email protected] - jakezdumb - 573689400

Killuminati

Last edited by Jakez; 02-18-2011 at 09:54 PM..
Jakez is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 02-18-2011, 09:57 PM   #11
ProG
Confirmed User
 
Join Date: Apr 2009
Posts: 1,319
Quote:
Originally Posted by Jakez View Post
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?
__________________
History will be kind to me for I intend to write it.
ProG is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 02-18-2011, 10:24 PM   #12
Jakez
Confirmed User
 
Jakez's Avatar
 
Industry Role:
Join Date: Jan 2004
Location: oddfuturewolfgangkillthemall!!!!!!!
Posts: 5,656
Quote:
Originally Posted by ProG View Post
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.
__________________
[email protected] - jakezdumb - 573689400

Killuminati
Jakez is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Post New Thread Reply
Go Back   GoFuckYourself.com - Adult Webmaster Forum > >

Bookmarks



Advertising inquiries - marketing at gfy dot com

Contact Admin - Advertise - GFY Rules - Top

©2000-, AI Media Network Inc



Powered by vBulletin
Copyright © 2000- Jelsoft Enterprises Limited.