Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    2

    Unanswered: row-by-row operation without cursor

    Hi all,
    I have a table with customer data. Ik would like to query this table for duplicate entries, based on certain criteria.
    My query to find these records:

    SELECT postcode, huisnr, huisnrtv
    FROM tblRelatie
    GROUP BY postcode, huisnr, huisnrtv
    HAVING COUNT(*) > 1

    So far so good. What I would like is to show some other data from the duplicate records. Example: suppose the above query
    finds 2 records having the same criteria (postcode, huisnr, huisnrtv). How can I select other data from the
    same table (e.g. name, date of birth) without using a cursor (is too expensive)? I thought of using a derived query but
    I can't figure out how to do it. My desired result looks something like this:

    postcode huisnr huisnrtv name date of birth
    --------------- -------- ---------- -------------
    1111AA 13 a Smith 12/3/70
    1111AA 13 a Clinton 10/2/72
    2222BB 22 Bloomberg 1/8/61
    2222BB 22 Bloomberg 9/2/79
    2222BB 22 Pataki 2/4/71
    etc.

    Thanks in advance.

    Diederik

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    select *
    from tblRelatie r
    join
    (
    SELECT postcode, huisnr, huisnrtv
    FROM tblRelatie
    GROUP BY postcode, huisnr, huisnrtv
    HAVING COUNT(*) > 1
    ) x on r.postcode=x.postcode and r.huisnr=x.huisnr and r.huisnrtv=x.huisnrtv

  3. #3
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: row-by-row operation without cursor

    Vezi daca merge asta:


    select tblRelatie.* from tblRelatie join
    (SELECT postcode, huisnr, huisnrtv
    FROM tblRelatie
    GROUP BY postcode, huisnr, huisnrtv
    HAVING COUNT(*) > 1) T1
    on tblRelatie.postcode=T1.postcode and tblRelatie.huisnr=T1.huisnr and tblRelatie.huisnrtv=T1.huisnrtv
    order by tblRelatie.postcode, tblRelatie.huisnr, tblRelatie.huisnrtv


    ionut calin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •