Select by grouped rows and order by date

Question

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 | {}

Answer 1

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 ;