SQL Server 2008 group by only rows which are in sequence

Question

I want a similar solution as per sql group by only rows which are in sequence

This is my data.

ID  SiteID  Latitude    Longitude
1   104 -6.197488   106.421873
2   823 -6.52733    106.849
3   823 -6.52733    106.849
4   823 -6.52733    106.849
5   823 -6.52733    106.849
6   823 -6.52733    106.849
7   104 -6.197488   106.421873
8   841 -6.30754    107.307

And I want to group record in same sequence only. E.g result (ID col doesn't really matter):

ID  SiteID  Latitude    Longitude
1   104 -6.197488   106.421873
6   823 -6.52733    106.849
7   104 -6.197488   106.421873
8   841 -6.30754    107.307

Tried following but it's not returning desired result.

        ;WITH YourTable AS
(
    SELECT ID, SiteID, Latitude, Longitude FROM @LatLng 
),
T AS
(
SELECT ID,
       SiteID,Latitude, Longitude,
       ROW_NUMBER() OVER (ORDER BY ID) - ROW_NUMBER() OVER (PARTITION BY SiteID ORDER BY ID) AS Grp
FROM YourTable
)
SELECT SiteID, Latitude, Longitude FROM T
GROUP BY SiteID,Latitude, Longitude, Grp

This is sample code to run above scenario :

  DECLARE @LatLng TABLE (ID  [int] IDENTITY(1,1) NOT NULL,SiteID nVARCHAR(50), Latitude float, Longitude float)
  INSERT INTO @LatLng(SiteID, Latitude, Longitude) VALUES ('104',1,106)
  INSERT INTO @LatLng(SiteID, Latitude, Longitude) VALUES ('823',-6,106)
   INSERT INTO @LatLng(SiteID, Latitude, Longitude) VALUES ('823',-6,106)
    INSERT INTO @LatLng(SiteID, Latitude, Longitude) VALUES ('823',-6,106)
 INSERT INTO @LatLng(SiteID, Latitude, Longitude) VALUES ('823',-6,106)
  INSERT INTO @LatLng(SiteID, Latitude, Longitude) VALUES ('104',1,106)
    INSERT INTO @LatLng(SiteID, Latitude, Longitude) VALUES ('814',-3,106)

  SELECT * FROM @LatLng

  ;with cte as(select *, row_number() over (partition by siteid order by id)as grp from @LatLng) select * from cte where grp=1

From the resultset you have shown, it looks like you need to group them by `SiteID` only. Possible solution: `;with cte as(select *, row_number() over (partition by siteid order by id)as grp from yourtable) select * from cte where grp=1` — Kiril Rusev, Dec 18 '13 at 16:21
Can you elaborate on *"isn't really working"*, this doesn't help much. — GarethD, Dec 18 '13 at 16:31

score 1 · Accepted Answer · answered Dec 18 '13 at 17:16

;
WITH    T AS ( SELECT   ID
                      , SiteID
                      , Latitude
                      , Longitude
                      , DENSE_RANK() OVER ( ORDER BY ID )
                        - DENSE_RANK() OVER ( PARTITION BY SiteID ORDER BY ID ) AS Grp
               FROM     @LatLng
             )
    SELECT  MAX(ID)
          , SiteID
          , Latitude
          , Longitude
    FROM    T
    GROUP BY SiteID
          , Latitude
          , Longitude
          , Grp
    ORDER BY MAX(ID)

Thanks Stuart, I need time to digest this now. :) – Randeep Singh Dec 18 '13 at 17:32 — Randeep Singh, Dec 18 '13 at 17:32

score 0 · Answer 2 · answered Dec 18 '13 at 17:05

0

;WITH T AS
(
   SELECT ID,  SiteID,Latitude, Longitude,
          ROW_NUMBER() OVER (PARTITION BY SiteID ORDER BY ID ASC) AS Grp
   FROM @LatLng 
)
SELECT SiteID, Latitude, Longitude 
FROM T
WHERE Grp = 1

answered Dec 18 '13 at 17:05

Hogan

69,564
10
76
117

How is this different from what they already have? (at the end of the code following "This is sample code to run above scenario") – Martin Smith Dec 18 '13 at 17:13
@MartinSmith - It isn't, for some reason the OP added that in after I posted this. I'm totally not clear what he wanted but I guess Stuart figured it out. – Hogan Dec 18 '13 at 18:29
The groups the OP wants are based on the order by id. Ignoring sequential ones with the same `SiteId` the pattern of SiteIds is `104,823,104,841` notice that `104` appears twice. – Martin Smith Dec 18 '13 at 18:40
@MartinSmith - I guess I'll have to test this later because this is what I'd expect my code to do -- show one row every time SiteID changes when ordered by ID. – Hogan Dec 18 '13 at 18:47

SQL Server 2008 group by only rows which are in sequence

2 Answers2