CODEWITHSUNDEEP
mysql
ATLChris
ATLChris

Reputation: 3296

MYSQL ORDER BY CASE Issue

I have a database like this:

-------------------------------------------------------------------
|   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:

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:

-------------------------------------------------------------------
|   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:

-------------------------------------------------------------------
|   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.

Upvotes: 30

Views: 91209

Answers (4)

lokendra birla
lokendra birla

Reputation: 36

SELECT * FROM `order_by_multiple` ORDER BY CASE WHEN custom_name='' OR custom_name is null THEN name END , CASE WHEN custom_name !='' THEN custom_name END;

Upvotes: 0

Esdras
Esdras

Reputation: 31

SELECT * FROM tablename 
WHERE id_one=27 OR id_two=27 
ORDER BY id ASC, timestamp_one DESC

Upvotes: 3

buru
buru

Reputation: 3210

SELECT 
   id_one,
   id_two,
   (CASE 
      WHEN id_one=27 THEN timestamp_one
      WHEN id_two=27 THEN timestamp_two    
   END) as timestamp
FROM tablename
ORDER BY timestamp DESC

Upvotes: 12

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171391

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

Upvotes: 71

Related Questions