Re: another question

From: ashleyT (anonymous_at_discussions.microsoft.com)
Date: 04/14/04

  • Next message: wantjoule_at_ncl.com: "Sorting results by year and date" 
    Date: Wed, 14 Apr 2004 09:36:11 -0700

    Here is what I have done to make it fit my query. Here are my records:
    store, deliverydate, itemnumber, qty
    006SS,04/15/2004,070100,018
    006SS,04/15/2004,090096,018

    006SS,04/15/2004,070100,018
    006SS,04/15/2004,090096,018
    (this should get deleted, exact same as 2 lines above)
    007SS,04/15/2004,030498,020
    007SS,04/15/2004,030498,020
    007SS,04/15/2004,030498,020
    007SS,04/15/2004,090495,020
    007SS,04/15/2004,090495,020
    (all lines should stay because it is not exact same.)

    selext a.*, cnt
        from tblItemOrder a join
        (select itemnumber, quantity, store, deliverydate, count(*) cnt
         from tblItemOrder
         group by itemnumber, quantity, store, deliverydate
         having count(*) > 1) b on a.itemnumber = b.itemnumber and
         a.quantity = b.quantity and a.store = b.store and a.deliverydate = b.deliverydate and b.cnt <>
         (select count(*)
          from tblItemOrder x
          group by store, deliverydate
          having x.store = b.store and x.deliverydate = b.deliverydate)

    I get all records that have duplicate lines, not just the ones with same count of duplicate records (storeno, deliverydate).

    Any ideas? I have thought about it many different ways and have not come up with a solution yet. Thanks again,

  • Next message: wantjoule_at_ncl.com: "Sorting results by year and date"