simple query for someone ! need a ickle bit of help

29 Jun 2006 8:44 PM

luna

i have a really simple (heh select ID,surname,postcode from table) query
that selects from a table 3 columns

ID (key field - auto increment)
Surname
Postcode

for example it returns

1 smith blah
2 smith blah
3 smith blah
4 jones test
5 arkwright bl00
5 arkwright bl00
6 smythe w000

i want to filter down on the results showing only the surnames that have
more than one ID
so it would only show :-

1 smith blah
2 smith blah
3 smith blah
5 arkwright bl00
5 arkwright bl00

tried using groupby and count > 1 but that wont show the ID

any clues ? - must be easy for someone!

mark

29 Jun 2006 9:16 PM

Ratcliff

I'm a bit confused by the arkwright name having two identical IDs when
you identify this as a key field. However, aside from that, try this
against the PUBS database.

select    distinct au_lname, au_id, zip
from authors
where    au_lname in (select au_lname
            from authors
            group by au_lname
            having count(au_lname) > 1)

Ringer    899-46-2035    84152
Ringer    998-72-3567    84152

Maybe this will work?

Philippa

luna wrote:
Show quote

> i have a really simple (heh select ID,surname,postcode from table) query
> that selects from a table 3 columns
>
> ID (key field - auto increment)
> Surname
> Postcode
>
> for example it returns
>
> 1 smith blah
> 2 smith blah
> 3 smith blah
> 4 jones test
> 5 arkwright bl00
> 5 arkwright bl00
> 6 smythe w000
>
> i want to filter down on the results showing only the surnames that have
> more than one ID
> so it would only show :-
>
> 1 smith blah
> 2 smith blah
> 3 smith blah
> 5 arkwright bl00
> 5 arkwright bl00
>
> tried using groupby and count > 1 but that wont show the ID
>
> any clues ? - must be easy for someone!
>
> mark

30 Jun 2006 5:42 AM

luna

yep my bad, it was late :) bad cut n paste, ill give your solution a try
when i go to work

cheers

mark

Show quote

"Ratcliff" <pratc***@co.alameda.ca.us> wrote in message
news:1151615815.366401.55870@j72g2000cwa.googlegroups.com...
> I'm a bit confused by the arkwright name having two identical IDs when
> you identify this as a key field. However, aside from that, try this
> against the PUBS database.
>
> select distinct au_lname, au_id, zip
> from authors
> where au_lname in (select au_lname
> from authors
> group by au_lname
> having count(au_lname) > 1)
>
>
> Ringer 899-46-2035 84152
> Ringer 998-72-3567 84152
>
> Maybe this will work?
>
> Philippa
>