| Author |
 Topic  |
|
|
anwarov
Starting Member
3 Posts |
 Posted - 05/04/2012 : 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 |
Edited by - anwarov on 05/04/2012 05:30:14
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 05/04/2012 : 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
USA
189 Posts |
Posted - 05/04/2012 : 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
India
47069 Posts |
Posted - 05/04/2012 : 18:52:59
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
anwarov
Starting Member
3 Posts |
Posted - 05/07/2012 : 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
India
47069 Posts |
Posted - 05/07/2012 : 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 - 05/11/2012 : 12:40:37
|
| Yes ! that works fine ! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 05/11/2012 : 15:37:19
|
wc
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Cleaning duplicate followed rows
