SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot Overgrow's Avatar
    Join Date
    Jan 2001
    Location
    Your basement
    Posts
    192
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sorting on two different fields?

    My duBois book is at home so I figured I'd ask here to see if anyone has a quick answer. Is there a way to sort on two different fields AND arrange them differently with DESC,ASC?

    For example... take this query (please)

    SELECT * FROM table WHERE category='$id' ORDER BY field1,field2 DESC

    That's great except that I want field1 sorted with DESC and then field2 sorted with ASC. This does not work:

    SELECT * FROM table WHERE category='$id' ORDER BY field1,field2 DESC,ASC

    Is there any way to do this without reading the results into an array and re-sorting it yourself?
    Overgrow the Government: The Edge Forums
    presented by Overgrow Marijuana Magazine

    *warning: adult content

  2. #2
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this:

    SELECT * FROM table WHERE category='$id' ORDER BY field1 DESC,field2 ASC

  3. #3
    SitePoint Zealot Overgrow's Avatar
    Join Date
    Jan 2001
    Location
    Your basement
    Posts
    192
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Damn, good try but no cigar...

    SELECT * FROM table WHERE category='$id' ORDER BY field1 DESC,field2 ASC

    returned the same results as

    SELECT * FROM table WHERE category='$id' ORDER BY field1,field2 ASC

    It ignored the DESC and sorted both with ASC...... any other ideas?

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Out of curiosity I tested this, and I was able to get it to work as long as there are many to one type of relationship between category and userID like in my case:

    PHP Code:
    mysqlselect userIDcategoryUPC from ad WHERE Type 999 ORDER BY userID DESCcategory ASC LIMIT 10;



    +---------------+----------------------+-------+
    userID        category             UPC   |
    +---------------+----------------------+-------+
    zman            Spam                 91602 |
    zman          Cars Trucks        06854 |
    zman          Cars Trucks        06854 |
    zman          Cars Trucks        06854 |
    zman          Cars Trucks        06854 |
    yman          Drapes               06854 |
    yman          Acorns               90046 |
    xman          Spam                 90046 |
    xman          Acorns               20007 |
    vman          Acorns               60085 |
    +---------------+----------------------+-------+ 
    I hope that helps
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Strange

    I went into Access2000 and used their QueryDesign feature and this is what I came up with:

    SELECT * FROM table WHERE category='$id' ORDER BY field1 DESC, field2;

    This query worked against my MySQL database via MyODBC in Access, but then again so did my original sugestion.

  6. #6
    SitePoint Zealot Overgrow's Avatar
    Join Date
    Jan 2001
    Location
    Your basement
    Posts
    192
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry guys, you were right. That does indeed work.. I'm not sure what I screwed up the first time but it does work just as expected now.

    thanks~!
    Overgrow the Government: The Edge Forums
    presented by Overgrow Marijuana Magazine

    *warning: adult content

  7. #7
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Overgrow I was running into difficulty recreating the problem but I think it has to do with the fact once the first field is sorted and then you try to sort the second field its going to screw with the order.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  8. #8
    SitePoint Zealot Overgrow's Avatar
    Join Date
    Jan 2001
    Location
    Your basement
    Posts
    192
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I could've sworn it didn't work the first time (what was I smokin??) but it's working now-- sorting on 3 fields properly.

  9. #9
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know but "pass it over my friend!"
    Please don't PM me with questions.
    Use the forums, that is what they are here for.


Bookmarks

Posting Permissions

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