Group consecutive rows of same value using time spans
The query determines each rows EndTime by using NOT EXISTS to make sure no other class or course of a different type is scheduled between a course range's StartTime and EndTime and then uses MIN and GROUP BY to find the StartTime.
The NOT EXISTS part ensures that there aren't "breaks" between the StartTime and EndTime ranges by searching for any rows that have an EndTime between StartTime and EndTime but belong to a different CourseName or CourseRoom.
SELECT
t0.ClassRoom,
t0.CourseName,
MIN(t0.StartTime),
t0.EndTime
FROM (
SELECT
t1.ClassRoom,
t1.CourseName,
t1.StartTime,
(
SELECT MAX(t2.EndTime)
FROM tableA t2
WHERE t2.CourseName = t1.CourseName
AND t2.ClassRoom = t1.ClassRoom
AND NOT EXISTS (SELECT 1 FROM tableA t3
WHERE t3.EndTime < t2.EndTime
AND t3.EndTime > t1.EndTime
AND (t3.CourseName <> t2.CourseName
OR t3.ClassRoom <> t2.ClassRoom)
)
) EndTime
FROM tableA t1
) t0 GROUP BY t0.ClassRoom, t0.CourseName, t0.EndTime
http://www.sqlfiddle.com/#!6/39d4b/9
Group consecutive rows based on one column
This is a gaps-and-islands problem. Use the difference of row_number():
select injourney, min(timestamp), max(timestamp)
from (select t.*,
row_number() over (order by timestamp) as seqnum,
row_number() over (partition by injourney, order by timestamp) as seqnum_i
from t
) t
group by injourney, (seqnum - seqnum_i)
order by min(timestamp);
How to group consecutive rows with same values in a result table into groups with date_from and date_until
If you enumerate the rows for each combination of f1, f2, and f3, then subtract that number of days from datum, then the value will be constant on adjacent days where the three columns are the same.
The rest is just aggregation:
select f1, f2, f3, min(datum), max(datum)
from (select t1.*,
row_number() over (partition by f1, f2, f3 order by datum) as seqnum
from test1 t1
) t1
group by f1, f2, f3, datum - seqnum * interval '1 day'
order by min(datum);
Here is a db<>fiddle.
SQL: Group By on Consecutive Records
Here's a kick at the can, only it may have syntax not available in SS2k. It was actually written on Oracle as I don't have that version of SS around anymore. The only catch might be the the select of a select...(it's been a while since I've used SS2k, so it's hard to remember what features weren't available back then.)
select min(weekenddate) as start_date, end_date, storecount
from (
select s1.weekenddate
, (select max(weekenddate)
from store_stats s2
where s2.storecount = s1.storecount
and not exists (select null
from store_stats s3
where s3.weekenddate < s2.weekenddate
and s3.weekenddate > s1.weekenddate
and s3.storecount <> s1.storecount)
) as end_date
, s1.storecount
from store_stats s1
) result
group by end_date, storecount
order by 1 desc
START_DATE END_DATE STORECOUNT
---------- ---------- ----------
2010-07-18 2010-07-25 359
2010-06-13 2010-07-11 358
2010-06-06 2010-06-06 359
2010-05-16 2010-05-30 360
Redshift - Group Table based on consecutive rows
One method is to peak at the previous row to see when the value changes. Assuming that valid_to and valid_from are really dates:
select id, class, min(valid_to), max(valid_from)
from (select t.*,
sum(case when prev_valid_to >= valid_from + interval '-1 day' then 0 else 1 end) over (partition by id order by valid_to rows between unbounded preceding and current row) as grp
from (select t.*,
lag(valid_to) over (partition by id, class order by valid_to) as prev_valid_to
from t
) t
) t
group by id, class, grp;
If the are not dates, then this gets trickier. You could convert to dates. Or, you could use the difference of row_numbers:
select id, class, min(valid_from), max(valid_to)
from (select t.*,
row_number() over (partition by id order by valid_from) as seqnum,
row_number() over (partition by id, class order by valid_from) as seqnum_2
from t
) t
group by id, class, (seqnum - seqnum_2)
SQL query to group consecutive records without destroying the chronological order
From Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row processing:
SELECT *
FROM table_test
MATCH_RECOGNIZE(
ORDER BY von
MEASURES
FIRST(gr) AS gr,
FIRST(i) AS i,
FIRST(von) AS von,
LAST(bis) AS bis
PATTERN (same_gr+)
DEFINE same_gr AS FIRST(gr) = gr
)
Which, for your sample data:
CREATE TABLE table_test (GR, I, VON, BIS) AS
SELECT 1, 'a', 1, 2 FROM DUAL UNION ALL
SELECT 2, 'b', 2, 3 FROM DUAL UNION ALL
SELECT 1, 'c', 3, 4 FROM DUAL UNION ALL
SELECT 1, 'd', 4, 5 FROM DUAL UNION ALL
SELECT 3, 'e', 5, 6 FROM DUAL;
Outputs:
GR I VON BIS 1 a 1 2 2 b 2 3 1 c 3 5 3 e 5 6 Group consecutive Rows into two rows with start and end
You can use
lead()andlag():select mv.*
from (select mv.*,
lag(value) over (partition by name order by timestamp) as prev_value,
lead(value) over (partition by name order by timestamp) as next_value
from MeasurementValues mv
where name = 'speed'
) mv
where (prev_value is null or prev_value <> value) or
(next_value is null or next_value <> value);Select the first row in the last group of consecutive rows
Assuming all columns
NOT NULL.SELECT *
FROM tbl t1
WHERE NOT EXISTS (
SELECT FROM tbl t2
WHERE t2.state <> t1.state
AND t2.datetime > t1.datetime
)
ORDER BY datetime
LIMIT 1;db<>fiddle here
NOT EXISTSis only true for the last group of peers. (There is no later row with a different state.)ORDER BY datetimeand take the first. Voilá.
Related Topics
Is There a Performance Difference Between Between and in with MySQL or in SQL in General
Intersection of Multiple Arrays in Postgresql
How Simultaneous Queries Are Handled in a MySQL Database
Execution Order of Conditions in SQL 'Where' Clause
How to Select a List of 10,000 Unique Ids from Dual in Oracle SQL
What Does (+) Do in Oracle SQL
SQL Conversion from Varchar to Uniqueidentifier Fails in View
Ms Access Date Triggers Emulation
Is It Necessary to Use # for Creating Temp Tables in SQL Server
Get Size of Large Object in Postgresql Query
Is Order Guaranteed When Inserting Multiple Rows with Identity
When Are Database Triggers Bad
Update and Select in One Query
How to Specify 'Default' as a SQL Parameter Value in Ado.Net
Regex Remove All Occurrences of Multiple Characters in a String