I read many questions with the greatest-n-per-group tag, but I can't find a better solution than the UNION ALL
trick for this SQL query :
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = '2014-02-13' and GroupName like 'GRP01%' ORDER BY Length DESC LIMIT 10)
UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = '2014-02-13' and GroupName like 'GRP04%' ORDER BY Length DESC LIMIT 10)
UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = '2014-02-13' and GroupName like 'GRP12%' ORDER BY Length DESC LIMIT 10)
UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = '2014-02-13' and GroupName like 'GRP15%' ORDER BY Length DESC LIMIT 10)
UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = '2014-02-13' and GroupName like 'GRP20%' ORDER BY Length DESC LIMIT 10);
I want to select the ten longest jobs for five different groups (GRP01%, GRP04%, GRP12%, GRP15% and GRP20%).
I'm using SQLite.
Elements in Start
and End
columns are ISO 8601 formatted.
I use (strftime('%s', End) - strftime('%s', Start)) as Length
to calculate the length of each job, then ORDER BY Length DESC LIMIT 10
for each group.
Do you know a simplest/better way to perform this query (with SQLite)?