![]() |
![]() |
![]() |
||||
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. |
![]() ![]() |
|
Discuss what's fucking going on, and which programs are best and worst. One-time "program" announcements from "established" webmasters are allowed. |
|
Thread Tools |
![]() |
#1 | |
Confirmed User
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:
![]() |
|
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#2 | |
see you later, I'm gone
Industry Role:
Join Date: Oct 2002
Posts: 14,065
|
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 ............
__________________
All cookies cleared! |
|
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#3 | |
partners.sexier.com
Industry Role:
Join Date: Jan 2007
Location: San Francisco, CA
Posts: 11,926
|
Quote:
![]() Id is your primary key... just order by views desc, id; |
|
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#4 |
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 :/ |
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#5 | |
see you later, I'm gone
Industry Role:
Join Date: Oct 2002
Posts: 14,065
|
Quote:
Editting in here, I had missed the last line so my answer did not make sense anymore ;p
__________________
All cookies cleared! |
|
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#6 | |
see you later, I'm gone
Industry Role:
Join Date: Oct 2002
Posts: 14,065
|
Quote:
__________________
All cookies cleared! |
|
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#7 |
<&(©¿©)&>
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 |
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#8 | |
Confirmed User
Industry Role:
Join Date: Jan 2004
Location: oddfuturewolfgangkillthemall!!!!!!!
Posts: 5,656
|
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.. |
|
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#9 | |
see you later, I'm gone
Industry Role:
Join Date: Oct 2002
Posts: 14,065
|
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
__________________
All cookies cleared! |
|
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#10 | |
Confirmed User
Industry Role:
Join Date: Jan 2004
Location: oddfuturewolfgangkillthemall!!!!!!!
Posts: 5,656
|
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! |
|
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#11 |
Confirmed User
Join Date: Apr 2009
Posts: 1,319
|
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. |
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#12 |
Confirmed User
Industry Role:
Join Date: Jan 2004
Location: oddfuturewolfgangkillthemall!!!!!!!
Posts: 5,656
|
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.
|
![]() |
![]() ![]() ![]() ![]() ![]() |