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
