Help on delete command

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
LVL 1
hidrauAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
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
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
lcohanConnect With a Mentor Database AnalystCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Dustin SaundersConnect With a Mentor Director of OperationsCommented:
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
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
(...)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
 
Steven KribbeConnect With a Mentor Software EngineerCommented:
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
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
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
 
hidrauAuthor Commented:
thanks all guys.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
_agx_Commented:
< Frasier Crane >  When you know the answer it's hard not to hit the buzzer.

Lol, so true!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.