Um, I understand sets; which is why I knew the naive group by wouldn't work. I guess I should have stated my question is HOW do I define the group so that in the order of I,L, clusters of common V values are a "group". I need to return the value of I and V, with the minimum and maximum L in that cluster.
I, L and V are data coming from an upstream source. I have no control over it, and would rather not write a mid-stream filter program to add an additional column, but can do that if there is no way to implement the "grouping" I want with the data as is.I is an arbitrary value, and L are sequential numbers for a given I. V is related properties for the given I. (Hopefully it doesn't make a difference, but I am simplifying the upstream data. V is actually a half dozen or so columns, but the cluster changes if any of the columns change, so for simplification, I just noted them as one value column.) Thanks for your help. David From: R Smith <rsm...@rsweb.co.za> To: sqlite-users at mailinglists.sqlite.org Sent: Thursday, February 18, 2016 4:55 AM Subject: Re: [sqlite] MIN/MAX query Let me see if I can unpuzzle this question - kindly say if it is not correctly assumed: ------------------------------------- I have a table: I? L V 1? 1 A 1? 2 A 1? 3 A 1? 4 B 1? 5 B 1? 6 A 2? 7 A 2? 1 C 2? 2? C I want to return the minimal and maximum L for each "group" of V in a given I. The result I want: I? MinL? ? MaxL V 1? ? 1? ? ? 3? ? ? A 1? ? 4? ? ? 5? ? ? B 1? ? 6? ? ? 7? ? ? A 2? ? 1? ? ? 2? ? ? C I know I can't just do? ? SELECT I, MIN(L) as MinL, MAX(L) as MaxL, V? FROM T? GROUP BY I, Vsince that would return min/max of 1 and 7 for A, instead of the 2 lines I need. So should I word the SELECT statement? --------------------------------------------------------- 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 (so that the order of your lines matter). Luckily, in SQLite there is a hidden column named row_id that should have that order already pegged - but you should really explicitly add one since your results depend on where in the list the values appear, not just what they are named. You should then, try to imply a group identifier, i.e another column that adds a group set or some way to know which A values belong together. This is not obvious in a SET. We might be able to find a way to determine grouping after-the-fact, based on data shape perhaps, but need more information then. What's the maximum number of lines that will be in a group? Does the L counter determine or exclude groups in any way? Might I and L when considered together offer some clue as to a next group starting? We could use the row-ID distance to narrow it into groups with items that have a maximum distance from each other, but then we need to know what maximum and minimum distances would be, etc. Howcome the current data set is ordered like that? Is it a time-based thing? like a logger? Some of these answers might help to make a query that can do it. This would be trivial in program code though. Cheers, Ryan _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
