Cleaning duplicate followed rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Cleaning duplicate followed rows

Author

Topic

anwarov
Starting Member

3 Posts

Posted - 2012-05-04 : 05:29:02

Hello everyone, I'm a beginner in SQL and i have difficulty to find solution for the folowing problem:

I'd like to do a SELECT which eliminate successive rows having the same "stat" and keep only the last occurrence.

To make myself better understood, I give you this example:

My table                                                 Results 
idLigne | id | stat                                    idLigne | id | stat  
   L1     1      A                                          L2     1      A
   L2     1      A                                          L6     1      B
   L3     1      B                                          L7     1      A
   L4     1      B                    ====>                 L8     1      B
   L5     1      B
   L6     1      B
   L7     1      A
   L8     1      B

Thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-05-04 : 07:37:08

select idLigne , id , stat from
(
select idLigne , id , stat, row_number() over (Partition by idLigne,stat order by idLigne DESC) as sno from table
) as t
where sno=1

Madhivanan

Failing to plan is Planning to fail

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-05-04 : 08:17:49

Try this script,

CREATE TABLE #TEMP(idLigne VARCHAR(10),id INT,stat VARCHAR(10))
INSERT INTO #TEMP VALUES ('L1', 1 , 'A')
INSERT INTO #TEMP VALUES ('L2', 1 , 'A')
INSERT INTO #TEMP VALUES ('L3', 1 , 'B')
INSERT INTO #TEMP VALUES ('L4', 1 , 'B')
INSERT INTO #TEMP VALUES ('L5', 1 , 'B')
INSERT INTO #TEMP VALUES ('L6', 1 , 'B')
INSERT INTO #TEMP VALUES ('L7', 1 , 'A')
INSERT INTO #TEMP VALUES ('L8', 1 , 'B')

SELECT ROW_NUMBER() OVER(order BY idLigne,id,stat ) as Row,idLigne,id,stat INTO #TEMP1 FROM #TEMP

SELECT T1.idLigne,T1.id,T1.stat,(SELECT T.idLigne FROM #TEMP1 AS T WHERE T.Row=(T1.Row+1) AND T.stat <> T1.stat) AS N INTO #TEMP2
FROM #TEMP1 AS T1

INSERT INTO #TEMP2 (idLigne,id,stat,N)
SELECT T1.idLigne,T1.id,T1.stat,'N' AS N FROM #TEMP1 AS T1 WHERE T1.Row = (SELECT MAX(Row) FROM #TEMP1)

SELECT T1.idLigne,T1.id,T1.stat FROM #TEMP2 AS T1 WHERE T1.N IS NOT NULL
ORDER BY idLigne

DROP TABLE #TEMP
DROP TABLE #TEMP1
DROP TABLE #TEMP2

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-04 : 18:52:59

[code]
SELECT MAX(t.idLigne) AS idLigne,id,stat
FROM Table t
OUTER APPLY (SELECT MIN(idLigne) AS MInID
FROM table
WHERE id = t.id
AND stat <> t.stat
AND idLigne > t.idLigne
)t1
GROUP BY id,stat,MInID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anwarov
Starting Member

3 Posts

Posted - 2012-05-07 : 04:22:01

thank you for your answers they give me some ideas. I finally being resolved the problem by using the following query:

with transformed as (
select idLigne,
statut,
row_number () over (order by idLigne) rn
from myTable
)
select myTable.*
from myTable
inner join transformed t1
on myTable.idLigne = t1.idLigne
left join transformed t2
on t1.rn = t2.rn - 1
and t1.statut = t2.statut
where t2.rn is null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-07 : 17:17:52

did the code work for you? I dont think it will give you sample output you posted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anwarov
Starting Member

3 Posts

Posted - 2012-05-11 : 12:40:37

Yes ! that works fine !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-11 : 15:37:19

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources