Say I have the following 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, the following command
SELECT
p1.relid AS relid,
p1.downid AS downid,
p1.sent AS last,
p1.`text` AS `text`
FROM
some_table AS p1
INNER JOIN (
SELECT
MAX(sent) AS MaxDate,
downid,
relid
FROM
some_table
WHERE
upid='1'
GROUP BY
downid,
relid
) AS p2
ON
p1.downid = p2.downid AND
p1.relid = p2.relid AND
p1.sent = p2.MaxDate
WHERE
p1.upid='1'
ORDER BY
`last` DESC
gives me the follwing output
relid downid last 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, I got that solution from elsewhere on stackoverflow) However, I'd like to get something output that looks like this:
relid downid last 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" is the number of rows that were grouped by "GROUP BY".
Is that at all possible, and if so, how?
An alternative would be to have two tables
sometable1
id relid upid downid last
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 then do something like
SELECT
sometable1.id AS cid1,
sometable1.relid AS relid,
sometable1.downid AS downid,
sometable1.last AS last,
sometable2.text AS `text`,
COUNT(SELECT
id
FROM
sometable2
WHERE
sometable2.cid=cid1) AS `count`
FROM
sometable1
LEFT JOIN
sometable2
ON
sometable2.cid=sometable1.id AND
sometable2.sent=sometable1.last
ORDER BY
last DESC
except that apparently produces a syntax error
You can just an an additional column to your subquery (p2
) as something like COUNT(*) AS CountOfRecords
, then you can select this in the outer query:
SELECT
p1.relid AS relid,
p1.downid AS downid,
p1.sent AS sent,
p1.`text` AS `text`,
p2.CountOfRecords
FROM
some_table AS p1
INNER JOIN (
SELECT
MAX(sent) AS MaxDate,
COUNT(*) AS CountOfRecords,
downid,
relid
FROM
some_table
WHERE
upid='1'
GROUP BY
downid,
relid
) AS p2
ON
p1.downid = p2.downid AND
p1.relid = p2.relid AND
p1.sent = p2.MaxDate
WHERE
p1.upid='1'
ORDER BY
`sent DESC