Mysql – Select by grouped rows and order by date

group bymysql-5.5

I have a table liked this:

session | created_at          | data
------------------------------------
12345   | 2018-10-10 08:10:10 | {}
13456   | 2018-10-11 09:12:13 | {}
12345   | 2018-10-10 08:12:13 | {}
14567   | 2018-08-09 10:11:12 | {}
14567   | 2018-08-09 03:09:11 | {}
12345   | 2018-10-10 08:24:12 | {}

There are thousands of rows. I'd like to group the rows by session but also order each group by timestamp. So if a new item is added to an existing session, that session group would be at the top.

I'd like to LIMIT this by groups of sessions. So ultimately I'd like to select groups of sessions and limit by group. This way, if I limit by 10, I'd potentially get 35 rows that comprises the 10 sessions.

Simply grouping by session, doesn't show all the rows, but 1 row per session. So this output I'd like to have like:

session | created_at          | data
------------------------------------
14567   | 2018-12-09 10:11:12 | {}
14567   | 2018-08-09 03:09:11 | {}
------------------------------------
13456   | 2018-10-11 09:12:18 | {}
13456   | 2018-10-11 09:12:13 | {}
------------------------------------
12345   | 2018-10-10 08:24:12 | {}
12345   | 2018-10-10 08:12:13 | {}
12345   | 2018-10-10 08:10:10 | {}

Best Answer

select t1.*
from table t1
          join (select t2.session, max(t2.created_at) max_created_at
                from table t2
                group by t2.session
                order by max_created_at desc
                limit 10 
               ) t3
          on t1.session = t3.session
order by t3.max_created_at desc, t3.session ;

Related Solutions

Mysql – Do id columns help (in speed) select statements

Since you mentioned your typical queries involve grouping by string fields, I assume you have many duplicate values in these columns (otherwise grouping makes no sense).

You can definitely improve performance by creating lookup table[s] for values stored in someData1, someData2, someData3 with integer primary key and changing original someData1 to someData1_id . You still need index on someData1_id, but taking into account number of rows , the new index will be much smaller than index on string field, and as a result , much faster...

MySQL code to show specific rows based on another…

Given this test data: (this is a hint on how to make a more readable question in the future):

create table inventory(
  id int unsigned primary key auto_increment,
  item int unsigned,
  warehouse int unsigned,
  qty int unsigned,
  price int unsigned,
  weight int unsigned,
  other int unsigned);

insert into inventory(item, warehouse, qty, price, weight, other) values
  (1,1,1,3,4,5),
  (1,2,6,3,4,5),
  (1,3,3,3,4,5);

> select item,warehouse,qty,price,weight,other from inventory;
+------+-----------+------+-------+--------+-------+
| item | warehouse | qty  | price | weight | other |
+------+-----------+------+-------+--------+-------+
|    1 |         1 |    1 |     3 |      4 |     5 |
|    1 |         2 |    6 |     3 |      4 |     5 |
|    1 |         3 |    3 |     3 |      4 |     5 |
+------+-----------+------+-------+--------+-------+

You can have the desired output with the following query:

> select distinct
  i.item,
  COALESCE(w1.qty,0) as w1,
  COALESCE(w2.qty,0) as w2,
  COALESCE(w3.qty,0) as w3,
  i.price,
  i.weight,
  i.other
from inventory i
  left join inventory w1 on (i.item = w1.item AND w1.warehouse=1)
  left join inventory w2 on (i.item = w2.item and w2.warehouse=2)
  left join inventory w3 on (i.item = w3.item and w3.warehouse=3);

+------+------+------+------+-------+--------+-------+
| item | w1   | w2   | w3   | price | weight | other |
+------+------+------+------+-------+--------+-------+
|    1 |    1 |    6 |    3 |     3 |      4 |     5 |
+------+------+------+------+-------+--------+-------+

COALESCE function and LEFT JOINs are for the case that there is no row for item, warehouse combination

Related Question