I'm trying to select a group, count the occurrences, and also return the oldest of the group. I was thinking along the lines of using a subquery with the immediate value, something like:
SELECT COUNT(submitdate), priority,
(SELECT submitdate FROM opentickets WHERE priority = priority ORDER BY submitdate ASC LIMIT 1)
FROM opentickets
WHERE assignee IS NULL
GROUP BY priority
But I get the same date for all priority groups. Does anyone know if its possible to use a column value like this? For instance, " priority = priority " becomes " priority = 'P1' " then 'P2', 'P3' and so on.
Desired Output
count priority submitdate
12 P1 "2017-04-03 10:48:14"
152 P2 "2017-03-23 02:24:07"
308 P3 "2017-03-03 05:06:43"
Use max
SELECT COUNT(submitdate), priority,
max(submitdate)
FROM opentickets
WHERE assignee IS NULL
GROUP BY priority
create table test(priority varchar(10), submitdate timestamp); insert into test values ('P1', '20170101'), ('P1', '20170102'), ('P1', '20170103'), ('P2', '20170301'), ('P2', '20170501'), ('P3', '20170501'), ('P3', '20170601'), ('P3', '20170705'); select priority, count(submitdate) as count_dates, max(submitdate) as max_date from test group by priority;✓ 8 rows affected priority | count_dates | max_date :------- | ----------: | :------------------ P2 | 2 | 2017-05-01 00:00:00 P3 | 3 | 2017-07-05 00:00:00 P1 | 3 | 2017-01-03 00:00:00
dbfiddle here