May 16, 2018 at 2:45 pm
Hello Expert
Below is data and what I attempt to achieve. It looks simple, but somehow I could not figure out how to do this without using some sort of looping. probably having a brain dead today. Any help would very appreciated
SELECT *
FROM (
VALUES (1,'B-6916','SEOK'),
(2,'B-6916','SEOK'),
(3,'B-6916','SEOK'),
(4,'B-6916','SEOK'),
(5,'B-6916','SEOK'),
(6,'B-6916','SEOK'),
(7,'B-6916','NEOK'),
(8,'B-6916','NEOK'),
(9,'B-6916','SEOK'),
(10,'B-6916','SEOK'),
(11,'B-6916','SEOK')
) V(ID, Name, Area)
Expected Result. Basically group data into 3 group, and get the first of each group.
1 B-6916 SEOK
7 B-6916 NEOK
9 B-6916 SEOK
May 16, 2018 at 2:59 pm
There are probably some better ways to do this but this just came to mind.
select *
from
(
select *
, NextArea = lag(Area, 1) over(order by (ID))
from
(
SELECT *
FROM (
VALUES (1,'B-6916','SEOK'),
(2,'B-6916','SEOK'),
(3,'B-6916','SEOK'),
(4,'B-6916','SEOK'),
(5,'B-6916','SEOK'),
(6,'B-6916','SEOK'),
(7,'B-6916','NEOK'),
(8,'B-6916','NEOK'),
(9,'B-6916','SEOK'),
(10,'B-6916','SEOK'),
(11,'B-6916','SEOK')
) V(ID, Name, Area)
) SampleData
) g
where g.Area <> g.NextArea
or g.NextArea is null
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 17, 2018 at 5:39 am
A self join should allow you to do this... I've shoved it into a CTE, but you might be better with a temp table or something so you can index it appropriately.
WITH DataCTE
AS
(
SELECT *
FROM (
VALUES (1,'B-6916','SEOK'),
(2,'B-6916','SEOK'),
(3,'B-6916','SEOK'),
(4,'B-6916','SEOK'),
(5,'B-6916','SEOK'),
(6,'B-6916','SEOK'),
(7,'B-6916','NEOK'),
(8,'B-6916','NEOK'),
(9,'B-6916','SEOK'),
(10,'B-6916','SEOK'),
(11,'B-6916','SEOK')
) V(ID, Name, Area)
)
SELECT cte1.*
FROM DataCTE cte1
LEFT JOIN DataCTE cte2 ON cte1.ID - 1 = cte2.ID
WHERE (cte1.Area != cte2.Area OR cte2.ID IS NULL)
May 17, 2018 at 5:39 am
Another way of doing it:;with myTable AS
(
SELECT *
FROM (
VALUES (1,'B-6916','SEOK'),
(2,'B-6916','SEOK'),
(3,'B-6916','SEOK'),
(4,'B-6916','SEOK'),
(5,'B-6916','SEOK'),
(6,'B-6916','SEOK'),
(7,'B-6916','NEOK'),
(8,'B-6916','NEOK'),
(9,'B-6916','SEOK'),
(10,'B-6916','SEOK'),
(11,'B-6916','SEOK')
) V(ID, Name, Area)
)
SELECT ID,
Name,
Area
FROM myTable t1
WHERE NOT EXISTS(SELECT *
FROM myTable t2
WHERE t2.id = t1.id-1
AND t2.Area = t1.Area
and t2.Name = t1.Name)
May 17, 2018 at 7:31 am
Both Farlzy & Jonathan's solution give me exact what I need. I also love Sean Lange's solution; However, I the box I am working on still running SQL2008 R2, so Lag is not an option at this time.
Thank you so very much everyone for this help. It is eye opening!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply