How do I.....?

12 Sep 2006 10:14 PM

hecsan07

Hey

I am a newbie to SQL programming. I am having difficulties finding repeated
records within a recordset. For instance, if the recordset has the following
fields, ID, Name, Address. Assume that none of this fields is a key field.
How do I query the recordset to find multiple combinations of ID and Name
fields.
The following is an example of what I am looking for. Assume the follwing
recordset:

01 Peter 1322 Peter's Way
02 John 13243 John's Road
01 Peter 1322 Peter's Way
02 John 13243 John's Road
04 Michael 232 Michael's Street

How do I write a query to get the following:
01 Peter 1322 Peter's Way
01 Peter 1322 Peter's Way
02 John 13243 John's Road
02 John 13243 John's Road

Thanks

12 Sep 2006 10:51 PM

Roy Harvey

SELECT W.*
FROM (select ID, Name
          from Whatever
         group by ID, Name
        having count(*) > 1) as K
JOIN Whatever as W
    ON K.ID = W.ID
   AND K.Name = W.Name
ORDER BY W.ID, W.Name

Roy Harvey
Beacon Falls, CT

On Tue, 12 Sep 2006 15:14:02 -0700, hecsan07 <hecsa***@hotmail.com>
wrote:

Show quoteHide quote

>Hey
>
>I am a newbie to SQL programming. I am having difficulties finding repeated
>records within a recordset. For instance, if the recordset has the following
>fields, ID, Name, Address. Assume that none of this fields is a key field.
>How do I query the recordset to find multiple combinations of ID and Name
>fields.
>The following is an example of what I am looking for. Assume the follwing
>recordset:
>
>01 Peter 1322 Peter's Way
>02 John 13243 John's Road
>01 Peter 1322 Peter's Way
>02 John 13243 John's Road
>04 Michael 232 Michael's Street
>
>How do I write a query to get the following:
>01 Peter 1322 Peter's Way
>01 Peter 1322 Peter's Way
>02 John 13243 John's Road
>02 John 13243 John's Road
>
>
>Thanks

13 Sep 2006 3:47 AM

Michael C

"hecsan07" <hecsa***@hotmail.com> wrote in message
news:E604A3C6-7C03-4C28-9636-6F4BE241AC39@microsoft.com...
> How do I write a query to get the following:
> 01 Peter 1322 Peter's Way
> 01 Peter 1322 Peter's Way
> 02 John 13243 John's Road
> 02 John 13243 John's Road

You'd be better off returning only 1 record and a count, eg

SELECT ID, Name, Address, COUNT(*) FROM Blah
GROUP BY ID,Name,Address
HAVING COUNT(*) > 1

(I have not tested this so please excuse any errors :-)

Show quoteHide quote

>
>
> Thanks
>