SQL Server : group by consecutive

Question

I have this table:

CREATE TABLE yourtable ( HevEvenementID INT, HjvNumeSequJour INT, HteTypeEvenID INT ); INSERT INTO yourtable VALUES (12074, 1, 66), (12074, 2, 66), (12074, 3, 5), (12074, 4, 7), (12074, 5, 17), (12074, 6, 17), (12074, 7, 17), (12074, 8, 17), (12074, 9, 17), (12074, 10, 5)

I need to group by consecutive HteTypeEvenID. Right now I am doing this:

SELECT HevEvenementID, MAX(HjvNumeSequJour) AS HjvNumeSequJour, HteTypeEvenID FROM (SELECT HevEvenementID, HjvNumeSequJour, HteTypeEvenID FROM yourtable y) AS s GROUP BY HevEvenementID, HteTypeEvenID ORDER BY HevEvenementID,HjvNumeSequJour, HteTypeEvenID

which returns this:

HevEvenementID HjvNumeSequJour HteTypeEvenID --------------------------------------------- 12074 2 66 12074 4 7 12074 9 17 12074 10 5

I need to group by consecutive HteTypeEvenID, to get this result:

HevEvenementID HjvNumeSequJour HteTypeEvenID ---------------------------------------------- 12074 2 66 12074 3 5 12074 4 7 12074 9 17 12074 10 5

Any suggestions?

consecutive HteTypeEvenID? They are all he same. VTC

paparazzo
– paparazzo

2017-10-27 13:44:40 +00:00
Commented Oct 27, 2017 at 13:44 — paparazzo
– paparazzo, Commented Oct 27, 2017 at 13:44

Gordon Linoff · Accepted Answer · 2017-10-28 01:12:16Z

In SQL Server, you can do this with aggregation and difference of row numbers:

select HevEvenementID, HteTypeEvenID, max(HjvNumeSequJour) from (select t.*, row_number() over (partition by HevEvenementID order by HjvNumeSequJour) as seqnum_1, row_number() over (partition by HevEvenementID, HteTypeEvenID order by HjvNumeSequJour) as seqnum_2 from yourtable t ) t group by HevEvenementID, HteTypeEvenID, (seqnum_1 - seqnum_2) order by max(HjvNumeSequJour);

I think the best way to understand how this works is by staring at the results of the subquery. You will see how the difference between the two values defines the groups of adjacent values.

thanks a lot, it's not exactly what I need but it's getting there.. I need the result to be ordered by hjvNumeSequ, your query is ordered by HteTypeEvenID
@MelodieGauthier . . . No. The original query was not ordered by anything, because it didn't have an order by clause. I added the order by.

Collectives™ on Stack Overflow

SQL Server : group by consecutive

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related