One obvious shortcut would be to factor out the common part of the select
using WITH
(Hopefully accurate) example:
with t as (
select GroupName, JobName, Start, End, Status, (strftime('%s', End) -
strftime('%s', Start)) as Length from ReportJobs where PlanDate =
'2014-02-13'
)
select * from (select * from t where GroupName like 'GRP01%' ORDER BY Length
DESC LIMIT 10)
UNION ALL
select * from (select * from t where GroupName like 'GRP04%' ORDER BY Length
DESC LIMIT 10)
UNION ALL
select * from (select * from t where GroupName like 'GRP12%' ORDER BY Length
DESC LIMIT 10)
UNION ALL
select * from (select * from t where GroupName like 'GRP15%' ORDER BY Length
DESC LIMIT 10)
UNION ALL
select * from (select * from t where GroupName like 'GRP20%' ORDER BY Length
DESC LIMIT 10);
-----Original Message-----
From: pihu...@free.fr
Sent: Tuesday, October 14, 2014 12:21 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length
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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
