Re: Help with Duplicate Query

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 02/10/05 

Date: Thu, 10 Feb 2005 01:04:01 +0100

On Wed, 9 Feb 2005 15:05:04 -0800, James Juno wrote:

>The problem I have is with the amount column. If all the columns in a row
>are duplicates and the amount column is between 1 - 100, it considered a
>duplicate. If the amount column is greater than 100, then it is no longer a
>duplicate. In other words Amount range between 1 - 100 and everything else
>is same is duplicate. If everything is same and amount is above 100, it's no
>longer a duplicate.

Hi James,

Do you mean that these are duplicates:

AA BB 67
AA BB 59

but these are not:

AA BB 167
AA BB 159

Further, should these be regarderd as duplicates:

AA BB 167
AA BB 167

To search for duplicates on all columns except amount for rows with amount
in the 1-100 range, simply add a where clause "amount between 1 and 100"
and remove the amount from the group by and select clause.

To find duplicates on all columns, but treating amounts below 100 as equal
(even if they are not), use something like this:

SELECT Col1, Col2, ...,
FROM MyTable
GROUP BY Col1, Col2, ...,
         CASE WHEN Amount < 100 THEN 1 ELSE Amount END
HAVING COUNT(*) > 1

(untested)

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Search for duplicates using MS Professional 2003 Excel
    ... "Geoff Lilley" wrote: ... and in the same row, is the second related column, which contains an amount ... the cell will say TRUE. ... To highlight which rows are duplicates, ...
    (microsoft.public.mac.office.excel)
  • Re: Search for duplicates using MS Professional 2003 Excel
    ... and in the same row, is the second related column, which contains an amount ... To highlight which rows are duplicates, ... yellow background color (or something else that really grabs your ... all the cells that are duplicates will be yellow. ...
    (microsoft.public.mac.office.excel)
  • Organizing photos
    ... I dumped a HUGE amount of photos and other files on a backup drive, ... now I want to go and organize them, deleting the duplicates but I am having ...
    (microsoft.public.windowsxp.photos)
  • Re: finding and removing duplicates rows
    ... If you want to eliminate duplicates, you have to add an IDENTITY ... or GUID column, or else you will delete both rows instead of one of ... Best, Hugo ... (Remove _NO_ and _SPAM_ to get my e-mail address) ...
    (microsoft.public.sqlserver.server)
  • Re: Help with Duplicate Query
    ... "Hugo Kornelis" wrote: ... >>The problem I have is with the amount column. ... > Hi James, ... should these be regarderd as duplicates: ...
    (microsoft.public.sqlserver.server)