Solved

Help on delete command

Posted on 2016-11-01
10
50 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 47

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

776 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