#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Location
    Italy
    Posts
    27
    Rep Power
    0

    [MySQL] Order by ASC or DESC


    In a prev thread (http://forums.devshed.com/mysql-help-4/order-by-timestamp-but-948152.html) I have ask how to order different ends auctions date and the suggests give me this well working query:

    Code:
      $query = "SELECT *
                FROM auctions
                WHERE suspended = 0 
                AND featured = 'y'
               ORDER 
                BY CASE WHEN starts < UNIX_TIMESTAMP()
                  AND ends > UNIX_TIMESTAMP()
                THEN 1
                  WHEN starts > UNIX_TIMESTAMP()
                THEN 2
              ELSE 3 
            END
         , ends ASC";
    where "starts" is the start time and "ends" is the end time of actions in timestamp format.

    The query is working, ok, but now I need to change the order of case 3 (ELSE 3).

    With this query I have the running auctions (ok), the future auctions (ok) and the terminate auction. These last are order from the most old to last but I need to show the last ends for first...

    I had try with ASC or DESC at the end of ELSE 3 but doesn't work or give me an error... some suggest?

    TY
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    Code:
     order
        by case when starts < UNIX_TIMESTAMP()
                 and ends > UNIX_TIMESTAMP()
               then 1
                when starts > UNIX_TIMESTAMP()
               then 2
               else 3 end,
           case when ends < UNIX_TIMESTAMP()
               then ends
               else 0 end desc,
           case when ends < UNIX_TIMESTAMP()
               then 0
               else ends end

    Comments on this post

    • =charlie= agrees : very good
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,442
    Rep Power
    1688
    Don't think you want the desc 'in the middle' of the case ...
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Location
    Italy
    Posts
    27
    Rep Power
    0
    Originally Posted by swampBoogie
    Code:
     order
        by case when starts < UNIX_TIMESTAMP()
                 and ends > UNIX_TIMESTAMP()
               then 1
                when starts > UNIX_TIMESTAMP()
               then 2
               else 3 end,
           case when ends < UNIX_TIMESTAMP()
               then ends
               else 0 end desc,
           case when ends < UNIX_TIMESTAMP()
               then 0
               else ends end

    THANK YOU VERY MUCH!
    This query works properly ... I think I need to return to study SQL ...

IMN logo majestic logo threadwatch logo seochat tools logo