Hello!
I'm trying to find a way to reduce the length of the following query using
SQLite:
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 can't find a solution without using this
"UNION ALL" trick.
Contents 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?
Thanks
Pierre
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
