Duplicate Values Problem

I trying to insert values from a temporary table into a permanent table. Th
e
problem is the temporary table has duplicate UpdateTime values (issues with
the database used to populate the temporary table) and the UpdateTime is a
primary key in the permanent table.
Is there a way I can remove, or exclude, the duplicate values in the
temporary table before inserting the values into the permanent table?
RUN_SER_NO = 635 is the bad actor in this case. Typically the duplicate
value with the larger RUN_SER_NO is the one to keep.
UpdateTime GRADE RUN_SER_NO
11-Aug-04 04:00 AA 634
11-Aug-04 05:00 AA 634
11-Aug-04 06:00 AA 634
11-Aug-04 07:00 VVV 636
11-Aug-04 07:00 VVV 635
11-Aug-04 08:00 VVV 635
11-Aug-04 08:00 VVV 636
11-Aug-04 09:00 VVV 636
11-Aug-04 09:00 VVV 635
11-Aug-04 10:00 VVV 635
11-Aug-04 10:00 VVV 636
11-Aug-04 11:00 VVV 636
11-Aug-04 12:00 VVV 636
11-Aug-04 13:00 VVV 636
11-Aug-04 14:00 VVV 636
11-Aug-04 15:00 VVV 636
Thanks in advance,
Raulselect UpdateTime from table_name group by UpdateTime having count(*) > 1
will give you the duplicates.You can remove them by using this query before
inserting into the permanent table.
"Raul" wrote:

> I trying to insert values from a temporary table into a permanent table.
The
> problem is the temporary table has duplicate UpdateTime values (issues wit
h
> the database used to populate the temporary table) and the UpdateTime is a
> primary key in the permanent table.
> Is there a way I can remove, or exclude, the duplicate values in the
> temporary table before inserting the values into the permanent table?
> RUN_SER_NO = 635 is the bad actor in this case. Typically the duplicate
> value with the larger RUN_SER_NO is the one to keep.
> UpdateTime GRADE RUN_SER_NO
> 11-Aug-04 04:00 AA 634
> 11-Aug-04 05:00 AA 634
> 11-Aug-04 06:00 AA 634
> 11-Aug-04 07:00 VVV 636
> 11-Aug-04 07:00 VVV 635
> 11-Aug-04 08:00 VVV 635
> 11-Aug-04 08:00 VVV 636
> 11-Aug-04 09:00 VVV 636
> 11-Aug-04 09:00 VVV 635
> 11-Aug-04 10:00 VVV 635
> 11-Aug-04 10:00 VVV 636
> 11-Aug-04 11:00 VVV 636
> 11-Aug-04 12:00 VVV 636
> 11-Aug-04 13:00 VVV 636
> 11-Aug-04 14:00 VVV 636
> 11-Aug-04 15:00 VVV 636
> Thanks in advance,
> Raul
>|||How about:
SELECT update_time, grade, run_ser_no
FROM tbl t1
WHERE t1.run_ser_no = ( SELECT MAX( t2.run_ser_no )
FROM tbl t2
WHERE t2.UpdateTime = t1.UpdateTime
AND t1.grade = t2.grade ) ;
Anith|||On Tue, 8 Feb 2005 14:11:04 -0800, Raul wrote:

>I trying to insert values from a temporary table into a permanent table. T
he
>problem is the temporary table has duplicate UpdateTime values (issues with
>the database used to populate the temporary table) and the UpdateTime is a
>primary key in the permanent table.
>Is there a way I can remove, or exclude, the duplicate values in the
>temporary table before inserting the values into the permanent table?
Hi Raul,
SELECT UpdateTime, Grade, Run_ser_no
FROM MyTable AS a
WHERE NOT EXISTS (SELECT *
FROM MyTable AS b
WHERE b.UpdateTime = a.UpdateTime
AND b.Run_ser_no > a.Run_ser_no)
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to all who replied!
These suggestions are really helpful.
Thanks again,
Raul
"Raul" wrote:

> I trying to insert values from a temporary table into a permanent table.
The
> problem is the temporary table has duplicate UpdateTime values (issues wit
h
> the database used to populate the temporary table) and the UpdateTime is a
> primary key in the permanent table.
> Is there a way I can remove, or exclude, the duplicate values in the
> temporary table before inserting the values into the permanent table?
> RUN_SER_NO = 635 is the bad actor in this case. Typically the duplicate
> value with the larger RUN_SER_NO is the one to keep.
> UpdateTime GRADE RUN_SER_NO
> 11-Aug-04 04:00 AA 634
> 11-Aug-04 05:00 AA 634
> 11-Aug-04 06:00 AA 634
> 11-Aug-04 07:00 VVV 636
> 11-Aug-04 07:00 VVV 635
> 11-Aug-04 08:00 VVV 635
> 11-Aug-04 08:00 VVV 636
> 11-Aug-04 09:00 VVV 636
> 11-Aug-04 09:00 VVV 635
> 11-Aug-04 10:00 VVV 635
> 11-Aug-04 10:00 VVV 636
> 11-Aug-04 11:00 VVV 636
> 11-Aug-04 12:00 VVV 636
> 11-Aug-04 13:00 VVV 636
> 11-Aug-04 14:00 VVV 636
> 11-Aug-04 15:00 VVV 636
> Thanks in advance,
> Raul
>