On 2014/10/13 23:21, pihu...@free.fr wrote:
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.
There is nothing wrong with this query, and the UNION ALL "trick" exists exactly for this purpose. I know it seems a bit convoluted
but it achieves the goal and it should be real fast. To get rid of the convolution I would implement a view, either temporary or
permanent, something like this:
CREATE TEMPORARY VIEW ReportJobLengths AS
SELECT GroupName, JobName, Start, End, Status,
(strftime('%s', End) - strftime('%s', Start)) as Length DESC
FROM ReportJobs where PlanDate = '2014-02-13'
ORDER BY Length;
SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP01%'
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP04%'
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP12%'
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP15%'
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP20%';
You may also create a persistent view and then always use it (without the
"temporary" keyword).
To get even more compact, I would go with Igor's SQL which is quite succint, but if those tables are big, that query will run quite
a bit slower - which is only a problem if the speed really matters.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
