How remove repetitive rows - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

How remove repetitive rows

Author

Topic

s_k_sameer
Starting Member

9 Posts

Posted - 2008-04-09 : 04:54:47

Boss,
Forget abt all.

My input table consist of ONLY TWO columns. And this is my table
(INPUT TABLE)
Time Action
17:42 SELL
17:43 BUY
17:44 SELL
17:45 SELL
17:46 SELL
17:47 BUY
17:48 BUY
17:49 SELL
17:50 SELL

When ever ACTION columns data is repeating remove that repetitive data rows.

(i mean to say Bold characters rows want to remove)

And finally my output should like this

(OUTPUT TABLE)
Time Action
17:42 SELL
17:43 BUY
17:44 SELL
17:47 BUY
17:49 SELL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-09 : 05:00:13

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 05:07:16

Well, for a start the records are not duplicate. They are just consecutive.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 05:12:55

[code]DECLARE @Sample TABLE (Time DATETIME, Action VARCHAR(4))

INSERT @Sample
SELECT '17:42', 'SELL' UNION ALL
SELECT '17:43', 'BUY' UNION ALL
SELECT '17:44', 'SELL' UNION ALL
SELECT '17:45', 'SELL' UNION ALL
SELECT '17:46', 'SELL' UNION ALL
SELECT '17:47', 'BUY' UNION ALL
SELECT '17:48', 'BUY' UNION ALL
SELECT '17:49', 'SELL' UNION ALL
SELECT '17:50', 'SELL'

SELECT s1.Time,
s1.Action
FROM @Sample AS s1
WHERE ISNULL((SELECT TOP 1 s2.Action FROM @Sample AS s2 WHERE s2.Time < s1.Time ORDER BY s2.Time DESC), '') <> s1.Action[/code]

E 12°55'05.25"
N 56°04'39.16"

s_k_sameer
Starting Member

9 Posts

Posted - 2008-04-09 : 07:39:42

thans for this query.

Thanks a lot.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 07:42:44

Please note that the performance will degrade siginficantly when there are a lot of records.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources