Delete from where count greater than 1, but keep a row

Question

I have a table like this (SQL2008):

id   url                  updated
1    http://url1.com      null
2    http://url2.com      <datetime>
3    http://url1.com      <datetime>

I want to delete ONE of the url1.com records (preferably where updated is not null, but its fine if it doesnt work that way)

I have this query, which deletes all the duplicates - but doesn't keep a record (which is where i'm confused):

DELETE FROM [table] WHERE url IN (
    SELECT url
    FROM [table]
    GROUP BY url
    HAVING COUNT(*) > 1 )

How do I limit the delete? Result would ideally be:

id   url                  updated
1    http://url1.com      null
2    http://url2.com      <datetime>

Right now it ends up like:

id   url                  updated
2    http://url2.com      <datetime>

Answer 1

Try this

DELETE FROM [table] WHERE id NOT IN (
    SELECT MAX(id)
    FROM [table]
    GROUP BY url
     )