I have a query that returns:
1
2
5
7
8
I'd like to group the rows like:
{1,2}
{5}
{7,8}
In other words I need to group the sequential values.
Any idea?
-
what's the exact logic behind ?Barbaros Özhan– Barbaros Özhan2018-08-30 22:12:50 +00:00Commented Aug 30, 2018 at 22:12
-
I need to identify all the broke sequence in a table.fdam– fdam2018-08-30 22:18:11 +00:00Commented Aug 30, 2018 at 22:18
Add a comment
|
3 Answers
This is a Gaps and Islands problem.
You can try to make row number then do some Calculate make the group by number.
CREATE TABLE T(
ID INT
);
INSERT INTO T VALUES (1);
INSERT INTO T VALUES (2);
INSERT INTO T VALUES (5);
INSERT INTO T VALUES (7);
INSERT INTO T VALUES (8);
Query 1:
WITH CTE AS (
SELECT Min(id) minid,MAX(ID) maxid
FROM (
SELECT ID,ID - ROW_NUMBER() OVER(ORDER BY ID) rn
FROM T
)t1
group by rn
)
SELECT (CASE WHEN minid = maxid
THEN CAST(maxid AS VARCHAR(50))
ELSE CONCAT(minid,',',maxid)
END) ID
FROM CTE
ORDER BY minid
| id |
|-----|
| 1,2 |
| 5 |
| 7,8 |
Based on your response to the reason for the need, that you are trying to locate broken sequences in a table. I would identify gaps like this. It doesn't give you explicitly what you requested, but what you requested is quite a bit more complex.
select col1
from mytable S
where not exists
(select col1
from mytable T
where T.col1 = S.col1 + 1)
This would return the last number that was sequential in each set prior to the gap.
To get to exactly what you wanted you could use the results of this table and some between statements to ultimately get to your explicit request or something more complex. You may be over-thinking it though.
Comments
This looks like an array would be suitable, so:
select array_agg(col order by col)
from (select t.*, row_number() over (order by col) as seqnum
from t
) t
group by (col - seqnum);
EDIT:
If you just want the start and end, use max() and min():
select min(col), max(col)
from (select t.*, row_number() over (order by col) as seqnum
from t
) t
group by (col - seqnum);
2 Comments
fdam
It also work bro, but I forgot to mention in my question that I may have a gap for example 1,2,3, and in this case, I just need to identify the start and end, in this case [1,3]. This SQL returns {1,2,3}. Thats why I choose the @D-Shih answer that return {1,3}. Thanks anyway!
Gordon Linoff
@fdam . . . It is pretty trivial to change this to just get the min and max.