how to select top 3 values of every column in mysql

Question

Hi I am having a table like this which contains the shift for the people on that day. I need it to be categorized on Shift basis

+------------+----------+-----------------+
| shift_date | name     | shift           |
+------------+----------+-----------------+
| 2016-07-01 | Swamy    | Morning Shift   |
| 2016-07-01 | Hitesh   | Morning Shift   |
| 2016-07-01 | Naresh   | Afternoon Shift |
| 2016-07-01 | Rambabu  | Afternoon Shift |
| 2016-07-01 | Srinivas | Night Shift     |
| 2016-07-01 | Ahmed    | Night Shift     |
| 2016-07-01 | Naveen   | Week Off        |
| 2016-07-01 | Karthik  | Week Off        |
+------------+----------+-----------------+

and I need a query to get result as

+------------+---------+-----------+----------+---------+
| shift_date | Morning | Afternoon | Night    | WeekOff |
+------------+---------+-----------+----------+---------+
| 2016-07-01 | Swamy   | Naresh    | Srinivas | Naveen  |
| 2016-07-01 | Hitesh  | Rambabu   | Ahmed    | Karthik |
+------------+---------+-----------+----------+---------+

I have written this query

 select
     shift_date,
     CASE shift
       WHEN shift <>'Morning Shift'
       then name
     end as Morning,
     CASE shift
       WHEN shift<>'Afternoon Shift'
       then name
     end as Afternoon,
     CASE shift
       WHEN shift<>'Night Shift'
       then name
     end as Night,
     CASE shift
       WHEN shift<>'Week Off'
       then name
     end as WeekOff
 from roster
 where shift_date ='2016-7-1'
 order by morning desc ,afternoon desc, night desc ,weekoff desc;

but its getting result as

+------------+---------+-----------+----------+---------+
| shift_date | Morning | Afternoon | Night    | WeekOff |
+------------+---------+-----------+----------+---------+
| 2016-07-01 | Swamy   | NULL      | NULL     | NULL    |
| 2016-07-01 | Hitesh  | NULL      | NULL     | NULL    |
| 2016-07-01 | NULL    | Rambabu   | NULL     | NULL    |
| 2016-07-01 | NULL    | Naresh    | NULL     | NULL    |
| 2016-07-01 | NULL    | NULL      | Srinivas | NULL    |
| 2016-07-01 | NULL    | NULL      | Ahmed    | NULL    |
| 2016-07-01 | NULL    | NULL      | NULL     | Naveen  |
| 2016-07-01 | NULL    | NULL      | NULL     | Karthik |
+------------+---------+-----------+----------+---------+

and I need result as

+------------+---------+-----------+----------+---------+
| shift_date | Morning | Afternoon | Night    | WeekOff |
+------------+---------+-----------+----------+---------+
| 2016-07-01 | Swamy   | Naresh    | Srinivas | Naveen  |
| 2016-07-01 | Hitesh  | Rambabu   | Ahmed    | Karthik |
+------------+---------+-----------+----------+---------+

Your desired result will not have a flexible logic. I suggest you implement this on your server-side language. — KaeL, Jul 18 '16 at 09:18
`MAX(CASE WHEN...) ...GROUP BY shiftdate` (although I don't understand the `<>` bit !?!? — Strawberry, Jul 18 '16 at 09:19
I tried MAX but its returning only one column and <> is nothing but not equal to — Ahmed, Jul 18 '16 at 09:21
Yes. Why is it Swamy followed by Hitesh and not the other way. In other words, what's your PRIMARY KEY? — Strawberry, Jul 18 '16 at 09:22
Possible duplicate of [MySQL - Rows to Columns](http://stackoverflow.com/questions/1241178/mysql-rows-to-columns) — Shadow, Jul 18 '16 at 09:22

Lakhan Baheti · Answer 1 · 2016-07-18T12:53:37.587

select m.shift_date  ,Morning , Afternoon 
from (select shift_date,name as Morning  from roster where shift = 'Morning Shift') as m, 
LEFT JOIN (select shift_date,name as Afternoon  from roster where shift = 'Afternoon Shift') as n on m.shift_date = n.shift_date

In your question u had all the shift date as same. So with this join on date will get name for shift_date also.

This is not an appropriate solution. Hope it may help a bit

score 0 · Answer 2 · answered Jul 18 '16 at 15:14

SELECT shift_date 
     , MAX(CASE WHEN  shift = 'morning shift' THEN name END) `morning`
     , MAX(CASE WHEN  shift = 'afternoon shift' THEN name END) `afternoon`
     , MAX(CASE WHEN  shift = 'night shift' THEN name END) `night`
     , MAX(CASE WHEN  shift = 'week off' THEN name END) `week off`
  FROM 
     ( SELECT x.* 
            , COUNT(*) rank
         FROM my_table x
         JOIN my_table y
           ON y.shift_date = x.shift_date
          AND y.shift = x.shift
          AND y.name <= x.name
        GROUP
           BY x.shift_date
            , x.shift
            , x.name
     ) n
 GROUP 
    BY shift_date,rank;

how to select top 3 values of every column in mysql

2 Answers2