Solved

Help on delete command

Posted on 2016-11-01
10
58 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 40

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 49

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
 
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 28

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

726 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