Complicated Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Complicated Stored Procedure

Author

Topic

beza
Starting Member

16 Posts

Posted - 2009-03-02 : 15:03:21

I am looking to create a stored procedure which I don't know the right approach for.

I have a table called Cars which have the columns CarID Counter and Time

there is no primary key so ID can appear more than once and whith different counter values and time values.
What I want to do is
Select from another table a couple of CarIDs and for those CarIDs I want to delete the records with the most recent time

for example

CarID Counter Time
1 10 14:35
1 20 14:37
2 30 14:50
2 40 14:20

I want to delete the first and the last row becuase for the IDs (1,2) it is the most recent time

I would really appreciate the help! I have no idea what to do!!
Thanks!!!

SQLforGirls
Starting Member

48 Posts

Posted - 2009-03-02 : 15:29:46

Your request doesn't quite match your example. You say you want to delete the most recent time, but in your example, you want to delete the records with the oldest time. Also, do you want to delete only one row per CarID? Or do you want to KEEP only one row per CarID?

Based on common sense (which I understand does not always apply in our world) I would guess that your problem is like this: You have multiple rows per CarID, and you want to keep only 1 row per CarID. So you want to keep only the most recent record per CarID, and delete all others.

Is that what you mean? If not, please re-explain.

thanks

subhash chandra
Starting Member

40 Posts

Posted - 2009-03-03 : 02:12:07

Even though your example does not match with your request but I think below query is what you are looking for:

DELETE Cars
FROM Cars INNER JOIN
(SELECT CarID, MAX(Time) AS Time FROM Cars GROUP BY CarID) tmpCars
ON Cars.CarID = tmpCars.CarID and Cars.Time = tmpCars.Time
WHERE Cars.CarID IN (<selected CarID>)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 09:55:23

or just use


DELETE t
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY CarID ORDER BY Time) AS Seq
FROM Cars
)t
WHERE t.Seq=1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 10:00:49

Most recent would be ORDER BY Time DESC, right?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 10:10:57

quote:
Originally posted by Peso

Most recent would be ORDER BY Time DESC, right?

E 12°55'05.63"
N 56°04'39.26"

yup. but look at his explanation. seems like what he need is to remove oldest one

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources