Re: SQL help needed

Hi, Peter.

        There's probably a slicker way to do this, but here's something that should work for you:

delete from t where (b, c, d) not in
 (select b, c, max(d) from t group by b, c)

However, if there are two entries for which the date d is exactly the same (time and all), then this will leave both of them.

HTH,
-Jeff Guttadauro

On 20 Aug 1999 13:53:20 GMT, "Peter Laursen" < ptl_at_edbgruppen.dk> wrote:

>In a table that looks like this:
>
>create table t(
> a int primary key,
> b int,
> c date,
> d date)
>
>I need to make (b,c) unique but duplicates exist. I want to delete all
>duplicates but one, leaving the row where d is newest.
>That is if t(1,1,01-01-99, 01-01-99) and t(2,1,01-01-99, 12-12-99) both
>exist, I want to keep the second. btw the timepart of the dates does
>matter, not just the date part.
>
>Could you help me with a delete that does the above?
>
>Thanks
>Peter
>
Received on Fri Aug 20 1999 - 14:26:28 CDT