Posted by Hugo Kornelis on 07/20/07 20:28

On Thu, 19 Jul 2007 20:28:34 -0000, Radu wrote:

>Hi.
>
>I have a "union" table which results of a union of two tables.
>Occasionally I could have duplicates, when the same PIN has been added
>to both tables, albeit at different Datees/Times, such as:
>
>PIN Name Added Date
>100411 A 7/11/2007 10:12:58 AM
>100411 A 7/17/2007 10:54:23 AM
>100413 B 7/11/2007 10:13:28 AM
>100413 B 7/17/2007 10:54:39 AM
>104229 C 7/6/2007 2:34:13 PM
>104231 D 7/6/2007 2:34:25 PM
>104869 E 6/10/2007 11:59:12 AM
>104869 E 6/22/2007 2:40:18 PM
>
>The question is - how can I delete by queries the first occurence
>(time-wise) of these duplicates - i.e. I would want to delete the
>first occurence of 100411 (A), the first occurence of 100413 (B), and
>the first occurence of 104869 (E) in the example above - records C and
>D show only once, so they are fine.
>
>Is there a MsAccess solution ? Is there a SQL-server solution ?
>
>Thank you very much !
>Alex

Hi Alex,

Your mention of a union makes me suspect that you don't want to remove
duplicate rows from a base table, but rather not include duplicate rows
in a view or query, without changing base data. If this suspicion is
correct, then try

SELECT PIN, Name, MIN("Added Date")
FROM YourView;

If I'm incorrect and you want to actually remove duplicated rows from a
base table, then see Erland's reply.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Back to original message]