Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2008
    Posts
    14

    Unanswered: sorting on multiple criteria

    I'm trying to sort on multiple criteria. e.g. varchar, varchar, int, bigint. However, I want bigint to be sort from smallest to largest (if case bigint = 0) list those entries below the rest.

    illustration:

    sitename, siteurl, 4, 2324
    sitename, siteurl, 5, 26248
    sitename, siteurl, 4, 732456
    sitename, siteurl, 6, 932400
    sitename, siteurl, 2, 9932412
    sitename, siteurl, 5, 23240876
    sitename, siteurl, 2, 0
    sitename, siteurl, 0, 0
    sitename, siteurl, 1, 0

    Is this possible?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    ORDER 
        BY sitename
         , siteurl
         , someint
         , CASE WHEN somebigint = 0 then 937 ELSE 0 END
         , somebigint
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2008
    Posts
    14
    Quote Originally Posted by r937
    Code:
    ORDER 
        BY sitename
         , siteurl
         , someint
         , CASE WHEN somebigint = 0 then 937 ELSE 0 END
         , somebigint
    What does the 937 do? Doh! or is that just your handle?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's an abitrary number greater than the previous value used to determine the custom order.
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it is not "abitrary"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2008
    Posts
    14

    I can't seem to get this to work, maybe its a php issue

    $query = "SELECT `SiteName`, `SiteURL`, `PageRank`, `AlexaRank` FROM `SocialSites` WHERE `SiteName` LIKE '%$trimmed%' ORDER BY `AlexaRank`";

    $numresults = mysql_query($query);
    $numrows=mysql_num_rows($numresults);

    ...works fine.

    no matter how I place: CASE WHEN `AlexaRank` = 0 then 937 ELSE 0 END within the string right before or after the ORDER BY statement I get an error.


  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    first of all, please dispense with the unnecessary and annoying backticks
    Code:
    SELECT SiteName
         , SiteURL
         , PageRank
         , AlexaRank 
      FROM SocialSites 
     WHERE SiteName LIKE '%$trimmed%' 
    ORDER 
        BY CASE WHEN AlexaRank = 0 THEN 937 ELSE 0 END 
         , AlexaRank
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Oct 2008
    Posts
    14
    Quote Originally Posted by r937
    first of all, please dispense with the unnecessary and annoying backticks
    Code:
    SELECT SiteName
         , SiteURL
         , PageRank
         , AlexaRank 
      FROM SocialSites 
     WHERE SiteName LIKE '%$trimmed%' 
    ORDER 
        BY CASE WHEN AlexaRank = 0 THEN 937 ELSE 0 END 
         , AlexaRank
    Still doesn't seem to work. I'm getting a Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource error.

    Thanks helping out though. I truly appreciate it and am loving db design!


  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that, sir, is not a mysql error, it is a generic php error

    run your query outside of php to see whether it works or not, or else turn on proper php error detection
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2008
    Posts
    14
    Thanks 937, I got it firing now ...you Rock! I think I'm going to bail for a while, I've been up all night schooling myself. I can't tell you how much I appreciate your help.

    Are you the dbforums master?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •