I need to delete ID '1' and '3' using SQL:
ID Customer DateTime
1 1 3/06/2005 03:00:00
2 1 3/06/2005 00:00:00
3 2 3/06/2005 03:00:00
4 2 3/06/2005 00:00:00

Thanks..

Try:

DELETE MyTable
WHERE id IN (1, 3);

HTH,

Plamen Ratchev
http://www.SQLStudio.com

I assume you don't just want

DELETE table_name WHERE ID IN (1,3)

Could you please indicate what version of SQL Server you are using? Also
what do you want to delete if there is another row:

5 2 3/06/2005 02:00

?

Here is answer that assumes SQL Server 2005, and also assumes you only want
to delete the newest row (as opposed to keeping only the oldest row, which
is different).


CREATE TABLE #foo(ID INT, Customer INT, [DateTime] DATETIME);

INSERT #foo SELECT 1,1,'20050306 03:00'
UNION ALL SELECT 2,1,'20050306'
UNION ALL SELECT 3,2,'20050306 03:00'
UNION ALL SELECT 4,2,'20050306';

SELECT * FROM #foo;

WITH RowsToDelete AS
(
SELECT ID FROM
(
SELECT ID,Customer,[DateTime],
[row_number] = ROW_NUMBER() OVER
(
PARTITION BY Customer
ORDER BY [DateTime] DESC
)
FROM #foo
) x
WHERE [row_number] = 1
)
DELETE RowsToDelete;

SELECT * FROM #foo;

DROP TABLE #foo;


However, please don't make us assume so much.




"morphius" <morphius@discussions.microsoft.com> wrote in message
news:0FF88A24-4B80-458D-A20E-92A2AF83A8BE@microsoft.com...
>I need to delete ID '1' and '3' using SQL:
> ID Customer DateTime
> 1 1 3/06/2005 03:00:00
> 2 1 3/06/2005 00:00:00
> 3 2 3/06/2005 03:00:00
> 4 2 3/06/2005 00:00:00
>
> Thanks..

Aaron, your assumption is correct. I just dont want to delete (1,3). I want
to keep the oldest row which is the one with 00:00:00. I am using SQL server
2000. Thanks for the .


"Aaron Bertrand [SQL Server MVP]" wrote:

> I assume you don't just want
>
> DELETE table_name WHERE ID IN (1,3)
>
> Could you please indicate what version of SQL Server you are using? Also
> what do you want to delete if there is another row:
>
> 5 2 3/06/2005 02:00
>
> ?
>
> Here is answer that assumes SQL Server 2005, and also assumes you only want
> to delete the newest row (as opposed to keeping only the oldest row, which
> is different).
>
>
> CREATE TABLE #foo(ID INT, Customer INT, [DateTime] DATETIME);
>
> INSERT #foo SELECT 1,1,'20050306 03:00'
> UNION ALL SELECT 2,1,'20050306'
> UNION ALL SELECT 3,2,'20050306 03:00'
> UNION ALL SELECT 4,2,'20050306';
>
> SELECT * FROM #foo;
>
> WITH RowsToDelete AS
> (
> SELECT ID FROM
> (
> SELECT ID,Customer,[DateTime],
> [row_number] = ROW_NUMBER() OVER
> (
> PARTITION BY Customer
> ORDER BY [DateTime] DESC
> )
> FROM #foo
> ) x
> WHERE [row_number] = 1
> )
> DELETE RowsToDelete;
>
> SELECT * FROM #foo;
>
> DROP TABLE #foo;
>
>
> However, please don't make us assume so much.
>
>
>
>
> "morphius" <morphius@discussions.microsoft.com> wrote in message
> news:0FF88A24-4B80-458D-A20E-92A2AF83A8BE@microsoft.com...
> >I need to delete ID '1' and '3' using SQL:
> > ID Customer DateTime
> > 1 1 3/06/2005 03:00:00
> > 2 1 3/06/2005 00:00:00
> > 3 2 3/06/2005 03:00:00
> > 4 2 3/06/2005 00:00:00
> >
> > Thanks..
>

Then I would probably solve the problem this way. Selecting into a work
table first will allow you to inspect the results... e.g. are these the rows
I want to keep?



SELECT ID, Customer, dt = MIN([DateTime])
INTO #foo
FROM table_name
WHERE ID IS NOT NULL
AND Customer IS NOT NULL
GROUP BY ID, Customer;

SELECT * FROM #foo;

-- if the above are the results you want to keep,
-- then uncomment this chunk and run again:

/*
DELETE table_name
WHERE ID NOT IN
(
SELECT ID FROM #foo
);
*/

DROP TABLE #foo;



In the future please give clearer requirements and always indicate what
version(s) you need to target. It prevents people like me from doing a
whole bunch of work for nothing (and gets you your answer quicker, too).






"morphius" <morphius@discussions.microsoft.com> wrote in message
news:E1F0B299-41C8-4988-A7E4-7EAC5F50C29A@microsoft.com...
> Aaron, your assumption is correct. I just dont want to delete (1,3). I
> want
> to keep the oldest row which is the one with 00:00:00. I am using SQL
> server
> 2000. Thanks for the .

Sorry, disregard, let me actually try it first.

Again, I have been way too spoiled by CTEs, and forget how to do this kind
of thing the old way.

Note that you may keep multiple results if you have ties with the same
customer having multiple rows having the same [DateTime] value. Bad name
for a column, by the way (well, so is ID, but DateTime is a data type).


CREATE TABLE #foo(ID INT, Customer INT, [DateTime] DATETIME);

INSERT #foo SELECT 1,1,'20050306 03:00'
UNION ALL SELECT 2,1,'20050306'
UNION ALL SELECT 3,2,'20050306 03:00'
UNION ALL SELECT 4,2,'20050306';

SELECT * FROM #foo;


SELECT ID
INTO #bar
FROM #foo f1
INNER JOIN
(
SELECT Customer, dt = MIN([DateTime])
FROM #foo
GROUP BY Customer
) f2
ON f1.Customer = f2.Customer
AND f1.[DateTime] = f2.dt;

SELECT * FROM #bar;

-- if the above are the results you want to keep,
-- then uncomment this chunk and run again:

/*
DELETE table_name
WHERE ID NOT IN
(
SELECT ID FROM #bar
);
*/

DROP TABLE #bar, #foo;



--A

Here is alternative with single query:

DELETE #foo
WHERE NOT EXISTS
(SELECT *
FROM #foo AS B
WHERE B.customer = #foo.customer
AND B.[datetime] > #foo.[datetime]);

Plamen Ratchev
http://www.SQLStudio.com

Yes, I only suggested multiple queries so that you can verify the results
without actually running the delete first. :-)




On 5/19/08 4:44 PM, in article
D1526EFD-7441-46FE-8F17-5F2C948C336F@microsoft.com, "Plamen Ratchev"
<Plamen@SQLStudio.com> wrote:

> Here is alternative with single query:
>
> DELETE #foo
> WHERE NOT EXISTS
> (SELECT *
> FROM #foo AS B
> WHERE B.customer = #foo.customer
> AND B.[datetime] > #foo.[datetime]);
>
> Plamen Ratchev
> http://www.SQLStudio.com

Rachev,
This one deleted everything!

"Plamen Ratchev" wrote:

> Here is alternative with single query:
>
> DELETE #foo
> WHERE NOT EXISTS
> (SELECT *
> FROM #foo AS B
> WHERE B.customer = #foo.customer
> AND B.[datetime] > #foo.[datetime]);
>
> Plamen Ratchev
> http://www.SQLStudio.com

Then perhaps you did not state your requirements correctly. Please post
create table statement, inserts for sample data, and expected results.

Take the sample script posted by Aaron and run the query. Here it is:

CREATE TABLE #foo(ID INT, Customer INT, [DateTime] DATETIME);

INSERT #foo SELECT 1,1,'20050306 03:00'
UNION ALL SELECT 2,1,'20050306'
UNION ALL SELECT 3,2,'20050306 03:00'
UNION ALL SELECT 4,2,'20050306';

SELECT * FROM #foo;


SELECT ID
INTO #bar
FROM #foo f1
INNER JOIN
(
SELECT Customer, dt = MIN([DateTime])
FROM #foo
GROUP BY Customer
) f2
ON f1.Customer = f2.Customer
AND f1.[DateTime] = f2.dt;

SELECT * FROM #bar;

DELETE #foo
WHERE NOT EXISTS
(SELECT *
FROM #foo AS B
WHERE B.customer = #foo.customer
AND B.[datetime] > #foo.[datetime]);

SELECT * FROM #foo;

DROP TABLE #bar, #foo;

This deletes only the two ids.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Plamen,
Actually it works. Thanks for the ..

"Plamen Ratchev" wrote:

> Then perhaps you did not state your requirements correctly. Please post
> create table statement, inserts for sample data, and expected results.
>
> Take the sample script posted by Aaron and run the query. Here it is:
>
> CREATE TABLE #foo(ID INT, Customer INT, [DateTime] DATETIME);
>
> INSERT #foo SELECT 1,1,'20050306 03:00'
> UNION ALL SELECT 2,1,'20050306'
> UNION ALL SELECT 3,2,'20050306 03:00'
> UNION ALL SELECT 4,2,'20050306';
>
> SELECT * FROM #foo;
>
>
> SELECT ID
> INTO #bar
> FROM #foo f1
> INNER JOIN
> (
> SELECT Customer, dt = MIN([DateTime])
> FROM #foo
> GROUP BY Customer
> ) f2
> ON f1.Customer = f2.Customer
> AND f1.[DateTime] = f2.dt;
>
> SELECT * FROM #bar;
>
> DELETE #foo
> WHERE NOT EXISTS
> (SELECT *
> FROM #foo AS B
> WHERE B.customer = #foo.customer
> AND B.[datetime] > #foo.[datetime]);
>
> SELECT * FROM #foo;
>
> DROP TABLE #bar, #foo;
>
> This deletes only the two ids.
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com
>

Thanks for the , Aaron.

"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, I only suggested multiple queries so that you can verify the results
> without actually running the delete first. :-)
>
>
>
>
> On 5/19/08 4:44 PM, in article
> D1526EFD-7441-46FE-8F17-5F2C948C336F@microsoft.com, "Plamen Ratchev"
> <Plamen@SQLStudio.com> wrote:
>
> > Here is alternative with single query:
> >
> > DELETE #foo
> > WHERE NOT EXISTS
> > (SELECT *
> > FROM #foo AS B
> > WHERE B.customer = #foo.customer
> > AND B.[datetime] > #foo.[datetime]);
> >
> > Plamen Ratchev
> > http://www.SQLStudio.com
>
>