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
I forgot to also factor out the ORDER BY. So, the updated query is:
-
with t as (
select GroupName, JobName, Start, End, Status,
(strftime('%s', End) - strftime('%s', Start)) as Length
from ReportJobs
where PlanDate = '2014-02-13'
order by Length desc
)
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
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
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 =
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 *
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
rg
> Subject: RE: [sqlite] SQL query - TOP
>
>
>
>
> Regarding: "It would appear that the "TOP" syntax is not supported by
> SQLite (maybe just a Microsoft thing?). However is there an
> alternative? "
>
> Take it to the "LIMIT", Mar
Regarding: "It would appear that the "TOP" syntax is not supported by
SQLite (maybe just a Microsoft thing?). However is there an
alternative? "
Take it to the "LIMIT", Mark.
See:
http://sqlite.org/lang_select.html
The LIMIT clause places an upper bound on the number of rows returned
in
9 matches
Mail list logo
