Jump to content

Using ORDER BY with multiple columns.


justin7410

Recommended Posts

I have a pretty beginner's question, but cant seem to get this right with what i try.

 

I had the same problem with using LIKE and OR with multiple fields and queries.

 

essentially i just want to ORDER BY using multiple fields i have in the DB.

 

my query is something like this

 

$query = "SELECT * FROM  content WHERE rating > 7
ORDER BY `votes` DESC, `release_date` DESC ";

now i understand with LIKE and OR you must separate arguments with (), but with ORDER BY i am not using any AND / OR so i though a comma should be sufficient. Yet, i can never seem to get this work properly.

 

any suggestions to get both fields to ORDER BY ?

 

thanks guys

 

Link to comment
Share on other sites

That is the correct syntax. What's not working?

 

now i understand with LIKE and OR you must separate arguments with ()

If you're talking about what I think you're talking about, that's only when you need to change how the default behavior, and it mostly just happens when mixing ANDs and ORs.

If you say something like "I want all posts by requinix or justin7410 and that have been made since June 1st" then you might write

WHERE username = "requinix" OR username = "justin7410" AND date >= "2013-06-01"
but what that actually says is "posts by requinix, or posts by justin7410 this month". Because ANDing happens before ORing*. If you add parentheses

WHERE (username = "requinix" OR username = "justin7410") AND date >= "2013-06-01"
then you override that and get the desired result.

 

* In terms of precedence and what happens when you mix the two together. They are still evaluated left to right.

Link to comment
Share on other sites

That is the correct syntax. What's not working?

 

If you're talking about what I think you're talking about, that's only when you need to change how the default behavior, and it mostly just happens when mixing ANDs and ORs.

If you say something like "I want all posts by requinix or justin7410 and that have been made since June 1st" then you might write

WHERE username = "requinix" OR username = "justin7410" AND date >= "2013-06-01"
but what that actually says is "posts by requinix, or posts by justin7410 this month". Because ANDing happens before ORing*. If you add parentheses

WHERE (username = "requinix" OR username = "justin7410") AND date >= "2013-06-01"
then you override that and get the desired result.

 

* In terms of precedence and what happens when you mix the two together. They are still evaluated left to right.

 

I apologize

 

but how is this pertaining to ORDER BY argument

 

from what i see your only addressing something that i already understand with AND / OR conditional , i am simply confused on ORDER BY sequence.

 

thanks again.

Link to comment
Share on other sites

but how is this pertaining to ORDER BY argument

Read the first sentence I wrote. You know, what came in my post before I quoted that thing you said and (I would argue) the comment one could deduce was what the remainder of my reply addressed.

 

from what i see your only addressing something that i already understand with AND / OR conditional

I was clarifying something you did not, in fact, understand correctly. Edited by requinix
Link to comment
Share on other sites

sorry i misread completely sorry for the post above.

 

the syntax is correct and i thought as much, what i am trying to do essentially is quite simple i thought.

 

i am trying to sort my results given from my $query( see OP ) to bring back the results by ORDER OF, of 2 separate fields in the DB.

 

i want it to return ORDER BY `votes`, `release_date`. which i am pretty certain my original query should be returning, but only sorts by the first field in the conditional.

 

as for your original reply, thank you, that was more helpful than i had originally thought. When i said i was having issues with AND/OR statements, i was referring to an issue i had posted, but i was already quite clear on what is needed for SQL statements, more so just confused as why im not getting the results i was hoping for.

 

thanks again req

 

i look forward to more feedback

Link to comment
Share on other sites

i want it to return ORDER BY `votes`, `release_date`. which i am pretty certain my original query should be returning, but only sorts by the first field in the conditional.

It'll sort by the first field first, and when two values are the same then it'll sort by the second field.

 

Seems like you need something more complicated than that. Have some sample data and how you would want it sorted?

 

[edit] Example:

          a ASC b ASC     a ASC b DESC     a DESC b DESC

a | b        a | b           a | b             a | b
--+--        --+--           --+--             --+--
1 | 1        1 | 1           1 | 3             3 | 3
2 | 2        1 | 2           1 | 2             2 | 3
3 | 3        1 | 3           1 | 1             2 | 2
1 | 2        2 | 2           2 | 3             1 | 3
1 | 3        2 | 3           2 | 2             1 | 2
2 | 3        3 | 3           3 | 3             1 | 1
Edited by requinix
Link to comment
Share on other sites

  • 1 month later...
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.