Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

2014-10-14 Thread Igor Tandetnik 

On 10/14/2014 3:51 AM, Mark Lawrence wrote:

On Tue Oct 14, 2014 at 09:25:20AM +0200, RSmith wrote:

To get even more compact, I would go with Igor's SQL which ... will run quite a 
bit slower


I'm a little curious about why you say a CTE statement is slower than a
VIEW for large tables.


My query uses neither views nor CTE. I'm a little curious about how a 
comment on the former could be construed to reflect in any way on the 
latter.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

2014-10-14 Thread Mark Lawrence 
On Tue Oct 14, 2014 at 09:25:20AM +0200, RSmith wrote:
> >
> >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.

I'm a little curious about why you say a CTE statement is slower than a
VIEW for large tables. I don't have large tables to test on but I get
the same query plan for both versions on small test tables. What
changes with size?

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

2014-10-14 Thread RSmith 

And of course there must be a LIMIT 10 added to every Union'd select (which I 
forgot):
SELECT * FROM ReportJobLengths R WHERE GroupName like 
'GRP01%' LIMIT 10
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP04%' LIMIT 
10
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP12%' LIMIT 
10
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP15%' LIMIT 
10
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP20%' LIMIT 
10;

etc.

On 2014/10/14 09:22, RSmith wrote:


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


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

2014-10-14 Thread RSmith 


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

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

2014-10-13 Thread Tony Papadimitriou 
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

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

2014-10-13 Thread Igor Tandetnik 

On 10/13/2014 5:21 PM, pihu...@free.fr wrote:

Do you know a simplest/better way to perform this query?


Something along these lines:

select * from ReportJobs r1
where rowid in (
  select r2.rowid from ReportJobs r2
  where substr(r2.GroupName, 1, 5) = substr(r1.GroupName, 1, 5)
and r2.PlanDate = '2014-02-13'
  order by (strftime('%s', r2.End) - strftime('%s', r2.Start)) desc 
limit 10

);

It'll probably be noticeably slower than your unrolled query, though.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

2014-10-13 Thread pihug12 
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