1

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)?

CL.
  • 173,858
  • 17
  • 217
  • 259
pihug12
  • 317
  • 3
  • 14
  • @CL. I already tried to adapt the queries from the answers you posted, without success. I have a single table (not two) and one value (`Length`) is calculated by the query. – pihug12 Sep 03 '14 at 13:52
  • related: [how to select first N row of each group?](http://stackoverflow.com/q/17991943/11654) – CL. Sep 03 '14 at 13:54
  • Then edit the question to show what you've tried. – CL. Sep 03 '14 at 13:54

0 Answers0