mysql - SQL: Get both the newest entry from each grouping as well as how many there are in each group -

mysql - SQL: Get both the newest entry from each grouping as well as how many there are in each group -

say have next table:

some_table id relid upid downid sent text 1 1 1 1 2014-09-13 12:23:11 sometext bla 2 1 1 2 2014-10-08 14:15:09 othertext 3 3 2 4 2014-10-08 20:34:57 foo 4 2 1 1 2014-10-09 04:14:15 bla 5 1 1 1 2014-10-15 17:24:15 more text

so far, next command

select p1.relid relid, p1.downid downid, p1.sent last, p1.`text` `text` some_table p1 inner bring together ( select max(sent) maxdate, downid, relid some_table upid='1' grouping downid, relid ) p2 on p1.downid = p2.downid , p1.relid = p2.relid , p1.sent = p2.maxdate p1.upid='1' order `last` desc

gives me follwing output

relid downid lastly text 1 1 2014-10-15 17:24:15 more text 2 1 2014-10-09 04:14:15 bla 1 2 2014-10-08 14:15:09 othertext

as expected. (btw, got solution elsewhere on stackoverflow) however, i'd output looks this:

relid downid lastly text count 1 1 2014-10-15 17:24:15 more text 2 2 1 2014-10-09 04:14:15 bla 1 1 2 2014-10-08 14:15:09 othertext 1

where "count" number of rows grouped "group by".

is @ possible, , if so, how?

an alternative have 2 tables

sometable1 id relid upid downid lastly 1 1 1 1 2014-10-15 17:24:15 2 1 1 2 2014-10-08 14:15:09 3 3 2 4 2014-10-08 20:34:57 4 2 1 1 2014-10-09 04:14:15 sometable2 id cid sent text 1 1 2014-09-13 12:23:11 sometext bla 2 2 2014-10-08 14:15:09 othertext 3 3 2014-10-08 20:34:57 foo 4 4 2014-10-09 04:14:15 bla 5 1 2014-10-15 17:24:15 more text

and like

select sometable1.id cid1, sometable1.relid relid, sometable1.downid downid, sometable1.last last, sometable2.text `text`, count(select id sometable2 sometable2.cid=cid1) `count` sometable1 left bring together sometable2 on sometable2.cid=sometable1.id , sometable2.sent=sometable1.last order lastly desc

except apparently produces syntax error

you can an additional column subquery (p2) count(*) countofrecords, can select in outer query:

select p1.relid relid, p1.downid downid, p1.sent sent, p1.`text` `text`, p2.countofrecords some_table p1 inner bring together ( select max(sent) maxdate, count(*) countofrecords, downid, relid some_table upid='1' grouping downid, relid ) p2 on p1.downid = p2.downid , p1.relid = p2.relid , p1.sent = p2.maxdate p1.upid='1' order `sent desc

mysql sql count group-by greatest-n-per-group