SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Dec 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ORDER BY CASE Issue

    OK, I have a database like this:

    Code:
    -------------------------------------------------------------------
    |   id_one   |   id_two   |   timestamp_one   |   timestamp_two   |
    -------------------------------------------------------------------
    |     27     |     35     |        9:30       |        NULL       |
    -------------------------------------------------------------------
    |     35     |     27     |        NULL       |        9:35       |
    -------------------------------------------------------------------
    |     27     |     35     |        9:34       |        NULL       |
    -------------------------------------------------------------------
    |     35     |     27     |        NULL       |        9:33       |
    -------------------------------------------------------------------
    I need pull all 4 rows ORDER BY 'timestamp_one' if 'id_one'=27 or ORDER BY 'timestamp_two' if 'id_one'=27.

    This is the statement I have now:

    Code:
    SELECT * FROM tablename WHERE id_one=27 OR id_two=27 ORDER BY CASE WHEN id_one=27 THEN timestamp_one END DESC, CASE WHEN id_two=27 THEN timestamp_two END DESC
    This works good in that is outputs this:

    Code:
    -------------------------------------------------------------------
    |   id_one   |   id_two   |   timestamp_one   |   timestamp_two   |
    -------------------------------------------------------------------
    |     27     |     35     |        9:30       |        NULL       |
    -------------------------------------------------------------------
    |     27     |     35     |        9:34       |        NULL       |
    -------------------------------------------------------------------
    |     35     |     27     |        NULL       |        9:33       |
    -------------------------------------------------------------------
    |     35     |     27     |        NULL       |        9:35       |
    -------------------------------------------------------------------
    But I need to two timestamp columns to order like they are one so it would order like this:

    Code:
    -------------------------------------------------------------------
    |   id_one   |   id_two   |   timestamp_one   |   timestamp_two   |
    -------------------------------------------------------------------
    |     27     |     35     |        9:30       |        NULL       |
    -------------------------------------------------------------------
    |     35     |     27     |        NULL       |        9:33       |
    -------------------------------------------------------------------
    |     27     |     35     |        9:34       |        NULL       |
    -------------------------------------------------------------------
    |     35     |     27     |        NULL       |        9:35       |
    -------------------------------------------------------------------
    I hope this makes sense. Essentially, I am trying to have two ORDER BY columns that are specific to a WHERE condition. Then once the correct ORDER BY column is chosen for that row, it orders the ROWS by the timestamp as a whole.

  2. #2
    SitePoint Member
    Join Date
    Dec 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Someone over at Stack Overflow came up with the answer. I wanted to post it in case anyone else wants to know.

    Code:
    SELECT id_one, id_two, timestamp_one, timestamp_two      
    FROM tablename 
    WHERE id_one = 27 
        OR id_two = 27 
    ORDER BY 
        CASE 
            WHEN id_one=27 THEN timestamp_one 
            WHEN id_two=27 THEN timestamp_two 
        END DESC

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    would i be correct in concluding from your sample data that whenever id_one is 27, then timestamp_2 is NULL? and whenever id_two is 27, then timestamp_1 is NULL?

    if so, you can simplify your query substantially

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Dec 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    would i be correct in concluding from your sample data that whenever id_one is 27, then timestamp_2 is NULL? and whenever id_two is 27, then timestamp_1 is NULL?

    if so, you can simplify your query substantially

    No, sometimes both ids are set and both timestamps are set. The query I am using is correct.


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
  •