Selecting last row when aggregating results in mysql
I was tasked with generating some usage reports for our Tracker Request. Request Tracker is a ticketing system that we use for multiple departments I've worked with. To do this, I take a nightly shot of ticket details changed during the day in another database. This approach decouples my reports from the internal database schema used by RT.
Among many other questions for the report, I need to report how many tickets were allowed in each month per department. In RT, the department is stored as a CustomField and my modeling follows this trend, as you can see in my request below. However, due to the way I collect images every night, I have multiple lines for the ticket, and the Department field can change throughout the month. I'm only interested in the last field of the Department. I don't know how to do this in a request.
I know I can use "GROUP BY" to reduce the results of my queries to one per ticket, but when I do, I don't know how to grab the last parameter of the Department. Since departments are all rows, MAX () doesn't get the last one. MySQL doesn't require you to use an aggregate function on the fields you select, but the results are vague (from my testing it looks like it can grab the first one in my MySQL version).
To illustrate, here are the results of a query that shows me two tickets and all of its Department field settings:
"ticket_num","date","QueueName","CF","CFValue","closed"
35750,"2009-09-22","IT_help","Department","",""
35750,"2009-09-23","IT_help","Department","",""
35750,"2009-09-24","IT_help","Department","",""
35750,"2009-09-25","IT_help","Department","",""
35750,"2009-09-26","IT_help","Department","",""
35750,"2009-10-02","IT_help","Department","",""
35750,"2009-10-03","IT_help","Department","",""
35750,"2009-10-12","IT_help","Department","",""
35750,"2009-10-13","IT_help","Department","",""
35750,"2009-10-26","IT_help","Department","Conference/Visitors","2009-10-26 10:10:32"
35750,"2009-10-27","IT_help","Department","Conference/Visitors","2009-10-26 10:10:32"
36354,"2009-10-20","IT_help","Department","",""
36354,"2009-10-21","IT_help","Department","",""
36354,"2009-10-22","IT_help","Department","FS Students",""
36354,"2009-10-23","IT_help","Department","FS Students",""
36354,"2009-10-26","IT_help","Department","FS Students","2009-10-26 12:23:00"
36354,"2009-10-27","IT_help","Department","FS Students","2009-10-26 12:23:00"
As we can see, both tickets were closed on the 26th, and both tickets had an empty Department field for several days when they first appeared. I have included my query below, you can see that I artificially limited the number of columns returned in the second half of the where statement:
SELECT d.ticket_num, d.date, q.name as QueueName, cf.name as CF, cfv.value as CFValue, d.closed
FROM daysCF dcf
INNER JOIN daily_snapshots d on dcf.day_id = d.id
INNER JOIN Queues q on d.queue_id = q.id
INNER JOIN CustomFieldValues cfv on dcf.cfv_id = cfv.id
INNER JOIN CustomFields cf on cf.id = cfv.field_id
WHERE cf.name = 'Department' and (d.ticket_num = 35750 or d.ticket_num = 36354)
ORDER by d.ticket_num, d.date
How can I modify this query to get a result set that tells me that one ticket was closed for "FS Students" in October and one ticket was closed for "Conference / Visitors"?
This is a "largest n-per-group" problem that often occurs when a stack overflow occurs.
Here's how I would solve it in your case:
SELECT d1.ticket_num, d1.date, q.name as QueueName,
cf.name as CF, cfv.value as CFValue, d1.closed
FROM daysCF dcf
INNER JOIN daily_snapshots d1 ON (dcf.day_id = d1.id)
INNER JOIN Queues q ON (d1.queue_id = q.id)
INNER JOIN CustomFieldValues cfv ON (dcf.cfv_id = cfv.id)
INNER JOIN CustomFields cf ON (cf.id = cfv.field_id)
LEFT OUTER JOIN daily_snapshots d2 ON (d1.ticket_num = d2.ticket_num AND d1.date < d2.date)
WHERE d2.id IS NULL AND cf.name = 'Department'
ORDER by d1.ticket_num, d1.date;
source to share
There is no LAST statement in Mysql, so you really need to do this using a temporary table.
CREATE TEMPORARY TABLE last_dates SELECT ticket_num, MAX(date) AS date
FROM daily_snapshots GROUP BY ticket_num
which gives you a table with the latest date for each ticket. Then, in your main request, join that table with ticket_num and date fields. This will filter out all rows for which the date is not the latest for the corresponding ticket number.
You may need an index on this temporary table, I'll leave that to you.
source to share