Thanks. You understood my intention, and confirmed my fear that I couldn't do
it efficiently in SQL.
David
From: Igor Tandetnik <igor at tandetnik.org>
To: sqlite-users at mailinglists.sqlite.org
Sent: Thursday, February 18, 2016 9:59 AM
Subject: Re: [sqlite] MIN/MAX query
On 2/18/2016 4:55 AM, R Smith wrote:
> First of, your intended results require a fundamentally wrong assumption
> about Sets. (SQL is essentially operating on SETs and sets have no order).
> You should really have another column, like a primary key ID that notes
> the position of each line
Looks like L is precisely such an ID. The OP is looking for runs of
identical (I, V) pairs when the table is scanned in order by (I, L) (or,
in other words, for each value of I, runs of V's in order by L).
If that's the case, then
select I, min(L) MinL, max(L) maxL, V
from MyTable t
group by I,
? (select min(L) from MyTable t2 where t2.I=t.I and t2.V=t.V and t2.L
<= t.L
? ? and not exists (
? ? ? select 1 from MyTable t3 where t3.I=t.I and t3.V != t.V
? ? ? and t3.L between t2.L and t.L)
? )
Though frankly, I'd implement this logic in application code. Just scan
the table with "select * from MyTable order by I, L" and compute the
runs in one pass, in linear time. The query above runs in O(N^3) time
(though an index on (I, L) might improve things) - only really suitable
as a toy example.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
