Remove duplicate based on condition

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

SQL Server 2005 Forums

Transact-SQL (2005)

Remove duplicate based on condition

Author

Topic

SQL Lady
Starting Member

3 Posts

Posted - 2010-09-14 : 10:55:40

I would like to eliminate a row based on the condition that it's the latest, duplicate row (based on the generated sqltime) given the account name is the same, the accountid is the same, the type is the same and the sqldate is the same.

Here is an example:
Accountid Account type sqltime sqldate
A1 ABC C 09284.47 20100914
A1 ABC C 092926.73 20100914

(eliminating the 2nd line)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-14 : 11:10:29

Try it:

delete dt
from
(
select
row_number() over (partition by Accountid, Account, type, sqldate order by sqltime desc) as rownum,
*
from your_table
)dt
where rownum = 1

No, you're never too old to Yak'n'Roll if you're too young to die.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-09-14 : 11:16:52

Accountid Account type sqltime sqldate
A1 ABC C 09284.47 20100914
A1 ABC C 092926.73 20100914

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

Is this what you meant your non-table to be like?

CREATE TABLE Foobar -- no key, not a table !
(account_id CHAR(2) NOT NULL,
account_name CHAR(1) NOT NULL,
account_type CHAR(6) NOT NULL,
something_time TIME NOT NULL,
something_date DATE NOT NULL,
..);

Since you have no key, what do you want to do about a group that is made of identical rows? Remove them all? Leave one? Why did you split date and time apart?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

SQL Lady
Starting Member

3 Posts

Posted - 2010-09-14 : 11:36:12

Thank you WebFred! I added a couple of conditions and it worked like a charm!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-14 : 11:40:14

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources