sql: Getting 3 highest values from table in column format

Question

I have a table(lets call it mytable) with the below structure. The table records close to 1 Million rows everyday.

id      date      timestamp  licenseid   storeid    deviceid    value
1     2015-06-12   17:36:15   lic0001       1         0add      52
2     2015-06-12   17:36:15   lic0002       1         0add      54
3     2015-06-12   17:36:15   lic0003       1         0add      53
4     2015-06-12   17:36:21   lic0001       1         0add      54
5     2015-06-12   17:36:21   lic0002       1         0add      59
6     2015-06-12   17:36:21   lic0003       1         0add      62
7     2015-06-12   17:36:21   lic0004       1         0add      55
8     2015-06-12   17:36:15   lic0001       1         0bdd      53
9     2015-06-12   17:36:15   lic0002       1         0bdd      52
10    2015-06-12   17:36:15   lic0003       1         0bdd      52
11    2015-06-12   17:36:15   lic0004       1         0bdd      50
12    2015-06-12   17:36:33   lic0002       1         0bdd      54
13    2015-06-12   17:36:33   lic0003       1         0bdd      54
14    2015-06-12   17:36:33   lic0004       1         0bdd      55
15    2015-06-12   17:36:33   lic0005       1         0bdd      60

I need to use the same data above and fetch data as below. What sql query should I use to get the below output but I only want the three highest values with the corresponding device. I know I need to use pivot but I am having difficulties framing the right query

id   date      timestamp   deviceid  storeid  deviceid1  deviceid2   deviceid3  value1   value2   value3  
1  2015-06-12   17:36:15     0add      1        lic001     lic002     lic003      52       54       53     
2  2015-06-12   17:36:21     0add      1        lic002     lic003     lic004      59       62       55
3  2015-06-12   17:36:15     0bdd      1        lic001     lic002     lic003      53       52       52      
4  2015-06-12   17:36:33     0bdd      1        lic002     lic004     lic005      54       55       60

For the last row the query could either return lic002 or lic003 as both values are same.

Answer 1

If acceptable to you, the easiest way is probably to use group_concat() and substring_index():

select min(id) as id, date, timestamp, storeid,
       substring_index(group_concat(device order by value desc), ',', 3) as devices,
       substring_index(group_concat(value order by value desc), ',', 3) as values
from mytable t
group by date, timestamp, storeid;

This puts the three values into a single column. You could actually then split them into separate columns if you like.

Otherwise, you need to enumerate the values and that gets complicated -- with a single query, you need either a subquery/non-equijoin (probably really expensive on your size of data) or variables.