CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Randeep Singh
Randeep Singh

Reputation: 1018

SQL Server 2008 group by only rows which are in sequence

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

Upvotes: 0

Views: 153

Answers (2)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

;
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)

Upvotes: 1

Hogan
Hogan

Reputation: 70531

;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

Upvotes: 0

Related Questions