Deleting Duplicate rows

[Rajasekar]

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 >=&#39;02/11/99&#39; 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

[MMS]

Hi,
U can try the following for
&#34;select
* from rates where ch_date >=&#39;02/11/99&#39; and
ch_date<=&#39;02/11/99&#39;&#34;

Declare @date datetime
select @date = &#39;02/11/99&#39;
select * from rates where date between @date and DATEADD(dd,1,@date)

best,
MMS

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 >=&#39;02/11/99&#39; 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

[Ajay]

This is regarding the date problem, I think you should specify the date in
mm-dd-yy hh-mm-ss format
Otherwise SQL server takes the same time by default i.e 00:00:00 for start and end date and dont return any of the rows.


Ajay

On 2/11/99 10:39:54 AM, MMS wrote:
> Hi,
U can try the following for
&#34;select
* from rates where
> ch_date >=&#39;02/11/99&#39; and

> ch_date<=&#39;02/11/99&#39;&#34;

Declare @date datetime
select @date =
> &#39;02/11/99&#39;
select * from rates where date between @date and
> DATEADD(dd,1,@date)

best,
MMS

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
> >=&#39;02/11/99&#39; 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

[patrick.fediere]

On your second issue. If you don&#39;t use the convert function you use the default dataformat mm/dd/yyyy hh:mm (AM/PM). As you know it&#39;s not really easy.
Each time I use convert and it works fine. Be aware of Year yy or yyyy the style is different.

For the delete issue, sorry.

Bye Patrick
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 >=&#39;02/11/99&#39; 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

[Robert Griffiths]

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 >=&#39;02/11/99&#39; 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, ...,

[Robert Griffiths]

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 >=&#39;02/11/99&#39; 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

THE DELETE PROBLEM

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.

Using a correlated subquery to delete duplicates would be difficult I would have thought, but it
is hard to say without looking at the data.

You could use a CURSOR to delete duplicates. You use the CURSOR to order the table in some way that
would ensure that duplicates follow one another. The easiest way to do this is to ORDER BY the
contents of the row.

You then step through each row and when you get to a duplicate row (i.e. one with all the column
values the same as the one just before) you delete it. I would have thought that is the simplest way.

DATES

Best way to do a day without time comparision is like this

CONVERT(char(11),date1) = CONVERT(char(11),date2)

is true if date1 and date2 are the same day.

[Rajasekar]

Thanks MMS

Your technique is very nice but the problem I get from and to date to generate a report from my client application and based on that if I build the
query dynamically, probabilily I may not be able to use your idea. If i use convert function my package should take care of multiple date formats.

Thanks

On 2/11/99 10:39:54 AM, MMS wrote:
> Hi,
U can try the following for
&#34;select
* from rates where
> ch_date >=&#39;02/11/99&#39; and

> ch_date<=&#39;02/11/99&#39;&#34;

Declare @date datetime
select @date =
> &#39;02/11/99&#39;
select * from rates where date between @date and
> DATEADD(dd,1,@date)

best,
MMS

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
> >=&#39;02/11/99&#39; 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