pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBeginner needs help
9+ messages / 9 participants
[nested] [flat]
* Beginner needs help
@ 2003-02-27 23:17 Aaron Chu <astrate@mac.com>
0 siblings, 7 replies; 9+ messages in thread
From: Aaron Chu @ 2003-02-27 23:17 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have a table which has a column of surnames (string) and I would like
to know how can I retrieve (SELECT) all the repeated surnames, i.e.
more than one person who has the same surname.
Thanks.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Beginner needs help
@ 2003-03-01 06:22 Greg Stark <gsstark@mit.edu>
parent: Aaron Chu <astrate@mac.com>
6 siblings, 0 replies; 9+ messages in thread
From: Greg Stark @ 2003-03-01 06:22 UTC (permalink / raw)
To: Aaron Chu <astrate@mac.com>; +Cc: pgsql-sql
Aaron Chu <astrate@mac.com> writes:
> I have a table which has a column of surnames (string) and I would like to know
> how can I retrieve (SELECT) all the repeated surnames, i.e. more than one
> person who has the same surname.
SELECT surname FROM table GROUP BY surname HAVING count(*) > 1
More generally, read up on GROUP BY.
--
greg
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Beginner needs help
@ 2003-03-01 08:41 Victor Yegorov <viy@pirmabanka.lv>
parent: Aaron Chu <astrate@mac.com>
6 siblings, 0 replies; 9+ messages in thread
From: Victor Yegorov @ 2003-03-01 08:41 UTC (permalink / raw)
To: pgsql-sql
* Aaron Chu <astrate@mac.com> [01.03.2003 03:22]:
> Hi,
>
> I have a table which has a column of surnames (string) and I would like
> to know how can I retrieve (SELECT) all the repeated surnames, i.e.
> more than one person who has the same surname.
>
> Thanks.
Say you have such a table:
create table person (
name varchar(10) not null,
surname varchar(20) not null
);
Now your select:
select
surname
from
person
group by surname
having count(surname) > 1;
--
Victor Yegorov
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Beginner needs help
@ 2003-03-01 22:55 Chad Thompson <chad@weblinkservices.com>
parent: Aaron Chu <astrate@mac.com>
6 siblings, 0 replies; 9+ messages in thread
From: Chad Thompson @ 2003-03-01 22:55 UTC (permalink / raw)
To: Aaron Chu <astrate@mac.com>; pgsql-sql
So, if I understand. You want a list of people whose surnames appear more
than once?
Something like
select count(*), surname
from mytable
group by surname
having count(*) > 1
This will show a unique list of all the surnames that appear more than 1
time.
HTH
Chad
----- Original Message -----
From: "Aaron Chu" <astrate@mac.com>
To: <pgsql-sql@postgresql.org>
Sent: Thursday, February 27, 2003 4:17 PM
Subject: [SQL] Beginner needs help
> Hi,
>
> I have a table which has a column of surnames (string) and I would like
> to know how can I retrieve (SELECT) all the repeated surnames, i.e.
> more than one person who has the same surname.
>
> Thanks.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Beginner needs help
@ 2003-03-02 00:23 Bruno Wolff III <bruno@wolff.to>
parent: Aaron Chu <astrate@mac.com>
6 siblings, 0 replies; 9+ messages in thread
From: Bruno Wolff III @ 2003-03-02 00:23 UTC (permalink / raw)
To: Aaron Chu <astrate@mac.com>; +Cc: pgsql-sql
On Thu, Feb 27, 2003 at 23:17:06 +0000,
Aaron Chu <astrate@mac.com> wrote:
> Hi,
>
> I have a table which has a column of surnames (string) and I would like
> to know how can I retrieve (SELECT) all the repeated surnames, i.e.
> more than one person who has the same surname.
select surname from whatevertable group by surname having count(*) > 1
order by surname;
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Beginner needs help
@ 2003-03-02 18:00 Tomasz Myrta <jasiek@klaster.net>
parent: Aaron Chu <astrate@mac.com>
6 siblings, 0 replies; 9+ messages in thread
From: Tomasz Myrta @ 2003-03-02 18:00 UTC (permalink / raw)
To: Aaron Chu <astrate@mac.com>; +Cc: pgsql-sql
Aaron Chu wrote:
>Hi,
>
>I have a table which has a column of surnames (string) and I would
like >to know how can I retrieve (SELECT) all the repeated surnames,
i.e. >more than one person who has the same surname.
>Thanks.
What exactly you want to do? To eliminate duplicates? Use then:
select surname from users group by surname;
Maybe you want to know which surnames are duplicated? Use then:
select surname from users group by surname having count(*)>1
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Beginner needs help
@ 2003-03-03 18:18 Josh Berkus <josh@agliodbs.com>
parent: Aaron Chu <astrate@mac.com>
6 siblings, 0 replies; 9+ messages in thread
From: Josh Berkus @ 2003-03-03 18:18 UTC (permalink / raw)
To: Aaron Chu <astrate@mac.com>; pgsql-sql
Aaron,
Thanks for an easy one:
> I have a table which has a column of surnames (string) and I would like
> to know how can I retrieve (SELECT) all the repeated surnames, i.e.
> more than one person who has the same surname.
To just get a list of repeated surnames:
SELECT surname, count(*) as names_count
FROM names_table
GROUP BY surname
HAVING count(*) > 1
To get the records these are attached to, call the above as a subquery:
SELECT names_table.*
FROM names_table
WHERE EXISTS ( SELECT surname, count(*)
FROM names_table
GROUP BY surname
HAVING count(*) > 1
AND surname = names_table.surname );
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Beginner needs help
@ 2003-03-08 23:20 Björn Lundin <bjorn.lundin.no.spam@swipnet.se>
parent: Aaron Chu <astrate@mac.com>
6 siblings, 1 reply; 9+ messages in thread
From: Björn Lundin @ 2003-03-08 23:20 UTC (permalink / raw)
To: pgsql-sql
Aaron Chu wrote:
> Hi,
>
> I have a table which has a column of surnames (string) and I would like
> to know how can I retrieve (SELECT) all the repeated surnames, i.e.
> more than one person who has the same surname.
select surname, count('a') from table
group by surname
having count('a') > 1
order by surname
/Björn
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Beginner needs help
@ 2003-03-09 22:42 Lee Harr <missive@frontiernet.net>
parent: Björn Lundin <bjorn.lundin.no.spam@swipnet.se>
0 siblings, 0 replies; 9+ messages in thread
From: Lee Harr @ 2003-03-09 22:42 UTC (permalink / raw)
To: pgsql-sql
In article <b4dtl8$1ejl$1@news.hub.org>, Björn Lundin wrote:
> Aaron Chu wrote:
>
>> Hi,
>>
>> I have a table which has a column of surnames (string) and I would like
>> to know how can I retrieve (SELECT) all the repeated surnames, i.e.
>> more than one person who has the same surname.
>
> select surname, count('a') from table
> group by surname
> having count('a') > 1
> order by surname
>
SELECT DISTINCT surname
FROM table t1, table t2
WHERE t1.surname = t2.surname
AND t1.oid != t2.oid
ORDER BY surname;
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2003-03-09 22:42 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-27 23:17 Beginner needs help Aaron Chu <astrate@mac.com>
2003-03-01 06:22 ` Greg Stark <gsstark@mit.edu>
2003-03-01 08:41 ` Victor Yegorov <viy@pirmabanka.lv>
2003-03-01 22:55 ` Chad Thompson <chad@weblinkservices.com>
2003-03-02 00:23 ` Bruno Wolff III <bruno@wolff.to>
2003-03-02 18:00 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-03 18:18 ` Josh Berkus <josh@agliodbs.com>
2003-03-08 23:20 ` Björn Lundin <bjorn.lundin.no.spam@swipnet.se>
2003-03-09 22:42 ` Lee Harr <missive@frontiernet.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox