Removing Duplicates in SQL - 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

Removing Duplicates in SQL

Author

Topic

k1vuc01
Starting Member

1 Post

Posted - 2014-06-04 : 22:51:17

I have table like this and I need to remove the duplicates selecting the Min Date
ID Date Alloc Prod Water Tub Cas
375 2011-10-10 00:00:00.000 0 0 0 14.7 0
375 2011-10-11 00:00:00.000 0 0 0 14.7 0
323 2014-04-24 00:00:00.000 0 0 0 352 555.7
323 2014-04-26 00:00:00.000 0 0 0 352 555.7
356 2014-04-01 00:00:00.000 0 0 0 318.8 471.1
356 2014-04-02 00:00:00.000 0 0 0 318.8 471.1
356 2014-04-03 00:00:00.000 0 0 0 318.8 471.1
356 2014-04-04 00:00:00.000 0 0 0 318.8 471.1
323 2014-05-20 00:00:00.000 0 0 0 649.1 976.9
323 2014-05-21 00:00:00.000 0 0 0 649.1 976.9
323 2014-05-22 00:00:00.000 0 0 0 649.1 976.9

I would like my result set to be like this:

375 2011-10-10 00:00:00.000 0 0 0 14.7 0
323 2014-04-24 00:00:00.000 0 0 0 352 555.7
356 2014-04-01 00:00:00.000 0 0 0 318.8 471.1
323 2014-05-20 00:00:00.000 0 0 0 649.1 976.9

Any help will be greatly appreciated

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-06-05 : 00:36:56

Try this.


CREATE TABLE K(D int ,Date datetime ,Alloc int,Prod int ,Water int,Tub float,Cas float)
INSERT INTO K VALUES
(375, '2011-10-10 00:00:00.000' ,0 ,0 ,0 ,14.7, 0),
(375,'2011-10-11 00:00:00.000' ,0 ,0 ,0 ,14.7 ,0),
(323, '2014-04-24 00:00:00.000' ,0 ,0 ,0 ,352, 555.7),
(323, '2014-04-26 00:00:00.000' ,0 ,0 ,0 ,352 ,555.7),
(356, '2014-04-01 00:00:00.000' ,0 ,0 ,0 ,318.8, 471.1),
(356, '2014-04-02 00:00:00.000' ,0 ,0 ,0 ,318.8, 471.1),
(356, '2014-04-03 00:00:00.000' ,0 ,0 ,0 ,318.8, 471.1),
(356, '2014-04-04 00:00:00.000' ,0 ,0 ,0 ,318.8, 471.1),
(323, '2014-05-20 00:00:00.000' ,0 ,0 ,0 ,649.1, 976.9),
(323, '2014-05-21 00:00:00.000' ,0 ,0 ,0 ,649.1, 976.9),
(323, '2014-05-22 00:00:00.000' ,0 ,0 ,0 ,649.1, 976.9)

SELECT * FROM K WHERE Date IN (SELECT MIN(Date)OVER (PARTITION BY D,Tub)FROM K )

DROP TABLE k

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-06-06 : 09:04:14

quote:

Originally posted by MuralikrishnaVeera

Try this.


CREATE TABLE K(D int ,Date datetime ,Alloc int,Prod int ,Water int,Tub float,Cas float)
INSERT INTO K VALUES
(375, '2011-10-10 00:00:00.000' ,0 ,0 ,0 ,14.7, 0),
(375,'2011-10-11 00:00:00.000' ,0 ,0 ,0 ,14.7 ,0),
(323, '2014-04-24 00:00:00.000' ,0 ,0 ,0 ,352, 555.7),
(323, '2014-04-26 00:00:00.000' ,0 ,0 ,0 ,352 ,555.7),
(356, '2014-04-01 00:00:00.000' ,0 ,0 ,0 ,318.8, 471.1),
(356, '2014-04-02 00:00:00.000' ,0 ,0 ,0 ,318.8, 471.1),
(356, '2014-04-03 00:00:00.000' ,0 ,0 ,0 ,318.8, 471.1),
(356, '2014-04-04 00:00:00.000' ,0 ,0 ,0 ,318.8, 471.1),
(323, '2014-05-20 00:00:00.000' ,0 ,0 ,0 ,649.1, 976.9),
(323, '2014-05-21 00:00:00.000' ,0 ,0 ,0 ,649.1, 976.9),
(323, '2014-05-22 00:00:00.000' ,0 ,0 ,0 ,649.1, 976.9)

SELECT * FROM K WHERE Date IN (SELECT MIN(Date)OVER (PARTITION BY D,Tub)FROM K )

DROP TABLE k

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

the way its written this will cause it to return records where date happens to be minimum date among any of the D,Tub groups
I think what you need is this


SELECT * 
FROM 
(
SELECT *,
MIN(Date)OVER (PARTITION BY D,Tub) AS MinDate
FROM K 
)t
WHERE MinDate = [Date]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources