On 2/11/99 5:31:38 AM, Rajasekar wrote:

> Hai

I have problem in deleting duplicate rows. I have a identity column
> in my table, if I try to use correlatted sub query with Delete command it
> gives error.

The other problem I have is I have a date column in my
> table and update that column with current date and time. If use a query to
> fetch a records on a particular day , it does not return any rows

select
> * from rates where ch_date >='02/11/99' and
> ch_date<=&#39;02/11/99&#39;

If I use convert also there is some other
> problems. Is there any way to force date checkings to be done excluding
> time.

Thanks

On the delete problem. I am unsure how you can have duplicate rows if you have an identity
column, for any row must be different from any other row in at least the identity column. Presumably
you are deleting rows that duplicate some other row except for the identity column.

One way to identify duplicates in a table is a query like this ...

SELECT count(1) as &#34;count&#34;,firstfield,secondfield, ... ,
FROM tablename
GROUP BY firstfield,secondfield, ... ,

This can be used for any number of fields

This will select duplicate rows and group them. In the &#34;Count&#34; column you will see the number
of duplicates.

To delete the duplicates, I would think you&#39;d need a temporary table made up from a query
based on the above, i.e.

SELECT count(1) as &#34;count&#34;,firstfield,secondfield, ...,