June 12, 2012 at 7:21 am
EDIT: Topic should have been Eliminating rows WITHOUT a change.
I have the following table...
DECLARE @test-2 TABLE
(
Id INT NOT NULL,
ModifiedDate DateTime NOT NULL,
Status INT NOT NULL
)
INSERT INTO @test-2 VALUES (1, '2010-1-1', 1)
INSERT INTO @test-2 VALUES (1, '2010-1-2', 1)
INSERT INTO @test-2 VALUES (1, '2010-1-3', 2)
INSERT INTO @test-2 VALUES (1, '2010-1-4', 1)
INSERT INTO @test-2 VALUES (1, '2010-1-5', 2)
INSERT INTO @test-2 VALUES (1, '2010-1-6', 2)
INSERT INTO @test-2 VALUES (1, '2010-1-7', 2)
INSERT INTO @test-2 VALUES (1, '2010-1-8', 1)
INSERT INTO @test-2 VALUES (1, '2010-1-9', 1)
INSERT INTO @test-2 VALUES (1, '2010-2-1', 2)
[/CODE]
I need a query that eliminates rows without status changes. The resulting recordset will have alternating statuses of 1,2,1,2,1,2, etc..
Thanks for your time!
June 12, 2012 at 8:51 am
TrippBlunschi (6/12/2012)
EDIT: Topic should have been Eliminating rows WITHOUT a change.I have the following table...
DECLARE @test-2 TABLE(
Id INT NOT NULL,
ModifiedDate DateTime NOT NULL,
Status INT NOT NULL
)
INSERT INTO @test-2 VALUES (1, '2010-1-1', 1)
INSERT INTO @test-2 VALUES (1, '2010-1-2', 1)
INSERT INTO @test-2 VALUES (1, '2010-1-3', 2)
INSERT INTO @test-2 VALUES (1, '2010-1-4', 1)
INSERT INTO @test-2 VALUES (1, '2010-1-5', 2)
INSERT INTO @test-2 VALUES (1, '2010-1-6', 2)
INSERT INTO @test-2 VALUES (1, '2010-1-7', 2)
INSERT INTO @test-2 VALUES (1, '2010-1-8', 1)
INSERT INTO @test-2 VALUES (1, '2010-1-9', 1)
INSERT INTO @test-2 VALUES (1, '2010-2-1', 2)
[/CODE]
I need a query that eliminates rows without status changes. The resulting recordset will have alternating statuses of 1,2,1,2,1,2, etc..
Thanks for your time!
Only need to know one more thing, which of the "duplicate" records do you want to retain? For instance of the first two records, which is the one to be kept?
June 12, 2012 at 9:09 am
Only need to know one more thing, which of the "duplicate" records do you want to retain? For instance of the first two records, which is the one to be kept?
The first record (ordered by date) should be kept. So, in the example, the first record (status of "1", 2010-1-1) would be kept.
Thanks!
June 14, 2012 at 7:13 am
Anyone?
Thanks!
June 14, 2012 at 8:11 am
There are a few ways to do this. Here is one of them.
select * from
(
select *, ROW_NUMBER() over(partition by ID order by ModifiedDate desc) as RowNum from @Test
) x
where RowNum = 1
_______________________________________________________________
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/
June 14, 2012 at 8:35 am
Sean Lange (6/14/2012)
There are a few ways to do this. Here is one of them.
select * from
(
select *, ROW_NUMBER() over(partition by ID order by ModifiedDate desc) as RowNum from @Test
) x
where RowNum = 1
Thanks for the response. I don't that's what I'm looking for as your example only returns a single row.
Below, the records that have --x beside them should be excluded in the result set.
INSERT INTO @Test VALUES (1, '2010-1-1', 1)
INSERT INTO @Test VALUES (1, '2010-1-2', 1) -- x
INSERT INTO @Test VALUES (1, '2010-1-3', 2)
INSERT INTO @Test VALUES (1, '2010-1-4', 1)
INSERT INTO @Test VALUES (1, '2010-1-5', 2)
INSERT INTO @Test VALUES (1, '2010-1-6', 2) -- x
INSERT INTO @Test VALUES (1, '2010-1-7', 2) -- x
INSERT INTO @Test VALUES (1, '2010-1-8', 1)
INSERT INTO @Test VALUES (1, '2010-1-9', 1) -- x
INSERT INTO @Test VALUES (1, '2010-2-1', 2)
This will give me a resultset that alternates 1,2,1,2,1,2,...
Thanks!
June 14, 2012 at 8:49 am
TrippBlunschi (6/14/2012)
Sean Lange (6/14/2012)
There are a few ways to do this. Here is one of them.
select * from
(
select *, ROW_NUMBER() over(partition by ID order by ModifiedDate desc) as RowNum from @Test
) x
where RowNum = 1Thanks for the response. I don't that's what I'm looking for as your example only returns a single row.
Below, the records that have --x beside them should be excluded in the result set.
INSERT INTO @Test VALUES (1, '2010-1-1', 1)
INSERT INTO @Test VALUES (1, '2010-1-2', 1) -- x
INSERT INTO @Test VALUES (1, '2010-1-3', 2)
INSERT INTO @Test VALUES (1, '2010-1-4', 1)
INSERT INTO @Test VALUES (1, '2010-1-5', 2)
INSERT INTO @Test VALUES (1, '2010-1-6', 2) -- x
INSERT INTO @Test VALUES (1, '2010-1-7', 2) -- x
INSERT INTO @Test VALUES (1, '2010-1-8', 1)
INSERT INTO @Test VALUES (1, '2010-1-9', 1) -- x
INSERT INTO @Test VALUES (1, '2010-2-1', 2)This will give me a resultset that alternates 1,2,1,2,1,2,...
Thanks!
Ahh well your description was a bit unclear. I think you are looking for a self join. Something like this?
select t1.* from @Test t1
left join @Test t2 on t1.ModifiedDate = t2.ModifiedDate + 1 and t1.Status = t2.Status
where t2.Id 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/
June 14, 2012 at 8:51 am
Or if your dates are not always in sequence you could do something like this.
;with cte as
(
select *, ROW_NUMBER() over(partition by ID order by ModifiedDate desc) as RowNum from @Test
)
select t1.* from cte t1
left join cte t2 on t1.RowNum = t2.RowNum + 1 and t1.Status = t2.Status
where t2.Id 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/
June 14, 2012 at 8:55 am
Thanks! Exactly what I was looking for!
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy