CODEWITHSUNDEEP
phpsqlsql-server
Romi
Romi

Reputation: 109

SQL OR logic sorting

I have columns for games PC=1/0, XBX=1/0, PS4=1/0. I'm returning the results based on the check box selected for PC, XBX and PS4 and getting the results.

If the platforms checked using pc=1 OR xbx=1 OR ps4=1.

I'm using PHP implode like this.

MySQL query for multiple checkbox values

The PHP implode works perfectly but it fails if the all plt.

The Query like 

SELECT  p1.date_id as rDate,     
        p1.game_name as gameName,         
        p1.release_type as rType,     
        p1.notes as rNotes,     
        on_pc as is_PC,     
        on_xb1 as is_XB1,     
        on_ps4 as is_PS4,     
        ie.[Event Name] as event_name     
FROM dbo.vw_fps_game_list p1     
LEFT JOIN dbo.Industry_Events_db ie ON ie.[Date] = p1.date_id   
WHERE (on_xb1=1 OR on_ps4=1 OR on_xb1)   
ORDER BY on_pc DESC

I need to show the results in this order if all 3 platforms are checked, then where (pc=1 OR xb1=1 OR ps4=1) needs to be sorted on the top, then where (pc =1 OR xb1=1 ) / (pc =1 OR ps4=1)/ (ps4 =1 OR xb1=1 ), then (pc =1) / (xb1=1)/ (ps4=1).

Any help would be highly appreciated.

Upvotes: 1

Views: 96

Answers (2)

OscarSosa
OscarSosa

Reputation: 89

According to your last paragraph, with the following script you could achieve what you are trying.

   SELECT p1.date_id       as rDate
        , p1.game_name     as gameName
        , p1.release_type  as rType
        , p1.notes         as rNotes
        , on_pc            as is_PC
        , on_xb1           as is_XB1
        , on_ps4           as is_PS4
        , ie.[Event Name]  as event_name
     FROM dbo.vw_fps_game_list p1
LEFT JOIN dbo.Industry_Events_db ie
       ON ie.[Date] = p1.date_id
    WHERE (on_xb1=1 or on_ps4=1 or on_xb1)
 ORDER BY CASE WHEN      on_pc  = 1
                     AND on_ps4 = 1
                     AND on_xb1 = 1                     THEN 1
               WHEN     (on_pc  = 1 AND on_xb1 = 1)
                     OR (on_pc  = 1 AND on_ps4 = 1)
                     OR (on_ps4 = 1 AND on_xb1 = 1)     THEN 2
               WHEN     (on_pc  = 1)
                     OR (on_xb1 = 1)
                     OR (on_ps4 = 1)                    THEN 3
               ELSE 4
           END ASC

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133390

You could use a case for set your order value 

  SELECT 
    p1.date_id as rDate
    , p1.game_name as gameName
    , p1.release_type as rType
    , p1.notes as rNotes
    , on_pc as is_PC
    , on_xb1 as is_XB1
    , on_ps4 as is_PS4
    , ie.[Event Name] as event_name     
    , case when on_pc = 1 and on_xb1 = 1 and on_ps4 = 1 then 0
           when on_pc_= 1 and on_xb1 = 1 and on_ps4 = 0 then 1
           when on_pc_= 1 and on_xb1 = 0 and on_ps4 = 0 then 2
               ELSE 3
       END as my_order
  FROM dbo.vw_fps_game_list p1     
  LEFT JOIN dbo.Industry_Events_db ie on ie.[Date] = p1.date_id   
  WHERE (on_xb1=1 or on_ps4=1 or on_xb1)   
  ORDER BY my_order

Upvotes: 3

Related Questions