May 19, 2015 at 8:45 pm
1Green
2Yellow
3Yellow
4Pink
5Green
6Yellow
7White
8White
9Pink
I want following output
1Green
2Yellow
3Pink
4Green
5Yellow
6White
7Pink
May 19, 2015 at 9:49 pm
That would be a select distinct.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 19, 2015 at 9:54 pm
This should do the trick...
-- test data --
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp;
CREATE TABLE #temp (
id INT,
color VARCHAR(15)
)
INSERT #temp (id,color) VALUES
(1,'Green'),
(2,'Yellow'),
(3,'Yellow'),
(4,'Pink'),
(5,'Green'),
(6,'Yellow'),
(7,'White'),
(8,'White'),
(9,'Pink')
-- the solution --
SELECT
ROW_NUMBER() OVER (ORDER BY x.id) AS id,
x.color
FROM (
SELECT
t.id,
t.color,
IIF(LAG(t.color, 1) OVER (ORDER BY t.id) = t.color, 1, 0) AS IsDupe
FROM
#temp t
) x
WHERE
x.IsDupe = 0
Edited to show the output...
idcolor
1Green
2Yellow
3Pink
4Green
5Yellow
6White
7Pink
May 19, 2015 at 9:59 pm
SQLRNNR (5/19/2015)
That would be a select distinct.
A "SELECT DISTINCT" won't do it. The INT column is unique is the original data and the same colors showing up more than once in the output.
From what I can tell, the OP just wants sequential repeats eliminated and the int column renumbered...
May 19, 2015 at 10:04 pm
Jason A. Long (5/19/2015)
SQLRNNR (5/19/2015)
That would be a select distinct.A "SELECT DISTINCT" won't do it. The INT column is unique is the original data and the same colors showing up more than once in the output.
From what I can tell, the OP just wants sequential repeats eliminated and the int column renumbered...
Looks to me like the int in the op is just a row number and not part of the data.
But looking at the color output, you would be correct on the sequential repeats.
That's what you get when the op does not post DDL and a good explanation of what they desire.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 19, 2015 at 10:11 pm
SQLRNNR (5/19/2015)
That's what you get when the op does not post DDL and a good explanation of what they desire.
+1
Agree 100%
May 19, 2015 at 11:53 pm
Jason A. Long (5/19/2015)
SQLRNNR (5/19/2015)
That's what you get when the op does not post DDL and a good explanation of what they desire.
+1
Agree 100%
If the OP just wants to sort out the duplicate a distinct would be enough BUT if he wants to sort out only the duplicates which occurs directly behind each other then you need the great solution from above.
May 20, 2015 at 10:07 am
Thank you guys...!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply