Re: Help with Duplicate Query

From: James Juno (JamesJuno_at_discussions.microsoft.com)
Date: 02/10/05 

Date: Thu, 10 Feb 2005 06:11:09 -0800

Hugo,

Thank you so much. Your suggestion helps a lot.

James

"Hugo Kornelis" wrote:

> 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)
  • Re: Help with Duplicate Query
    ... If everything is same and amount is above 100, ... should these be regarderd as duplicates: ... Best, Hugo ... (Remove _NO_ and _SPAM_ to get my e-mail address) ...
    (microsoft.public.sqlserver.server)
  • 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: error handling in user input: is this natural or just laborious
    ... a huge amount to think about there. ... over time (as james said was true in his own ... case), and with practice, the relative weights of these two burdens ... benefits of readability and maintainability easier to enjoy. ...
    (comp.lang.python)