Solved

Help on delete command

Posted on 2016-11-01
10
45 Views
Last Modified: 2016-11-01
Hello guys

I have a table with three columns

idclie  -   client  - date

I have two rows where id and client are repeated. I want to delete all rows where and let the one row where date is lesser.

how can I do that?

example of rows:

1 - Alexander Garcia - 10/05/2016
1 - Alexander Garcia - 10/03/2016
1 - Alexander Garcia - 21/01/2016

I use the firebird database, but you can send me an example in SQL that I will try it or make some changes to run it in my DB

Thanks
Alexandre
0
Comment
Question by:hidrau
10 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 41 total points
ID: 41868637
>I have two rows where id and client are repeated. I want to delete all rows where and let the one row where date is lesser.

;with cte as (
   SELECT id, client, ROW_NUMBER() OVER (PARTITION BY id, client ORDER BY [date]) as row_number
   FROM YourTableNameGoesHere) 
DELETE FROM cte WHERE row_number > 1

Open in new window

Source:  SQL Server Delete Duplicate Rows
1
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 41 total points
ID: 41868639
Technically they are not duplicated as the "time-stamp" column value you listed is different right? so to leave only 1 out of three put them in a temp table and SELECT TOP 1 * from it where that date is = to whatever you want to keep.
1
 
LVL 12

Assisted Solution

by:Dustin Saunders
Dustin Saunders earned 41 total points
ID: 41868642
You could delete all dupes for one ID, except the highest date with something similar to this:
DELETE FROM table
WHERE id = 1 AND date NOT IN (SELECT TOP 1 date FROM table WHERE id = 1 ORDER BY date)

Open in new window

1
 
LVL 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 41 total points
ID: 41868645
(...)let the one row where date is lesser.
Lesser means the more recent or the oldest one?
Anyway you can do it by running the following (replace MAX for MIN and change the '<' to '>' if you want to keep the oldest date):
DELETE FROM tableName
WHERE date < (SELECT MAX(t2.date)
               FROM tableName t2
               WHERE tableName.idclie = t2.idclie)

Open in new window

NOTE: I didn't test it so please run first in a test environment before running it in Production.
1
 
LVL 52

Accepted Solution

by:
_agx_ earned 254 total points
ID: 41868651
Edit:  Wow... a lot of responses added while I wrote this up

DELETE FROM dupe
FROM  YourTable dupe
WHERE EXISTS
        (
            SELECT 1
            FROM   YourTable keep
            WHERE  keep.client = dupe.client
            AND    keep.date < dupe.date
        )
1
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
LVL 4

Assisted Solution

by:Steven Kribbe
Steven Kribbe earned 41 total points
ID: 41868693
I do not know the exact table names but i think you want to retain the row with the smallest date. You may want to run this query multiple times because it only deletes the row with the highest date

DELETE TableA
FROM   TableA a
       INNER JOIN (select id, client, max(thedate) as thedate, count(*) group by id, client having count(*) > 1) b
               ON b.id= a.id and b.client = a.client and a.thedate = b.thedate
1
 
LVL 24

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 41 total points
ID: 41868808
Try..

--

CREATE TABLE testDeleet
(
	 ID INT
	,NMAE VARCHAR(100)
	,Dt DATETIME
)
GO


INSERT INTO testDeleet VALUES

(1 ,'Alexander Garcia' , '05/10/2016'),
(1 ,'Alexander Garcia' , '03/10/2016'),
(1 ,'Alexander Garcia' , '01/21/2016')

GO

--

Open in new window



Query to delete the duplicate values

---Try 1

--


DELETE j
FROM testDeleet j
WHERE j.Dt <> ANY
             ( 
                SELECT TOP 1 Dt a
                FROM testDeleet e
                WHERE e.ID = j.ID
				ORDER BY e.Dt ASC

             )
 

Open in new window


O/p

---------------------------

/*------------------------
DELETE j
FROM testDeleet j
WHERE j.Dt <> ANY
             (
                SELECT TOP 1 Dt a
                FROM testDeleet e
                WHERE e.ID = j.ID
                        ORDER BY e.Dt ASC
             )
------------------------*/

(2 row(s) affected)
1
 
LVL 1

Author Closing Comment

by:hidrau
ID: 41868856
thanks all guys.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41868920
>Wow... a lot of responses added while I wrote this up
< Frasier Crane >  When you know the answer it's hard not to hit the buzzer.

Thanks for the split, good luck with your project.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41868936
< Frasier Crane >  When you know the answer it's hard not to hit the buzzer.

Lol, so true!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now