[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Help on delete command

Posted on 2016-11-01
10
Medium Priority
?
86 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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 164 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 164 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 13

Assisted Solution

by:Dustin Saunders
Dustin Saunders earned 164 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 164 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 1016 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 164 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 32

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 164 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 66

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

649 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