pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedExistential quantifier
6+ messages / 3 participants
[nested] [flat]
* Existential quantifier
@ 2009-10-09 22:53 Dag-Erling Smørgrav <des@des.no>
2009-10-09 23:06 ` Re: Existential quantifier Stephan Szabo <sszabo@megazone.bigpanda.com>
2009-10-09 23:11 ` Re: Existential quantifier Richard Albright <ralbright@insiderscore.com>
0 siblings, 2 replies; 6+ messages in thread
From: Dag-Erling Smørgrav @ 2009-10-09 22:53 UTC (permalink / raw)
To: pgsql-sql
Consider the attached schema (filmstars.sql), which is a poorly designed
database of films and actors. The following query gives me a list of
films in which either Charlie or Martin Sheen starred:
select fs.film.title, fs.film.year
from fs.film left join fs.star on fs.film.id = fs.star.film
where fs.star.last = 'Sheen'
group by fs.film.title, fs.film.year;
Is there a way to do this without the "group by" clause?
DES
--
Dag-Erling Smørgrav - des@des.no
Attachments:
filmstars.sql (text/plain, 2.2K) download
drop schema fs cascade;
create schema fs;
create table fs.film (
id serial not null primary key,
title varchar not null unique,
year integer not null
);
create table fs.star (
id serial not null primary key,
film integer not null references fs.film(id),
last varchar not null,
first varchar not null,
unique (film, last, first)
);
insert into fs.film(title, year) values ('Apocalypse Now', 1979);
insert into fs.star(film, last, first)
select id, 'Sheen', 'Martin' from fs.film where title = 'Apocalypse Now';
insert into fs.star(film, last, first)
select id, 'Brando', 'Marlon' from fs.film where title = 'Apocalypse Now';
insert into fs.star(film, last, first)
select id, 'Duvall', 'Robert' from fs.film where title = 'Apocalypse Now';
insert into fs.star(film, last, first)
select id, 'Ford', 'Harrison' from fs.film where title = 'Apocalypse Now';
insert into fs.film(title, year) values ('Blade Runner', 1982);
insert into fs.star(film, last, first)
select id, 'Ford', 'Harrison' from fs.film where title = 'Blade Runner';
insert into fs.star(film, last, first)
select id, 'Young', 'Sean' from fs.film where title = 'Blade Runner';
insert into fs.star(film, last, first)
select id, 'Hauer', 'Rutger' from fs.film where title = 'Blade Runner';
insert into fs.star(film, last, first)
select id, 'Hannah', 'Daryl' from fs.film where title = 'Blade Runner';
insert into fs.film(title, year) values ('Platoon', 1986);
insert into fs.star(film, last, first)
select id, 'Sheen', 'Charlie' from fs.film where title = 'Platoon';
insert into fs.star(film, last, first)
select id, 'Dafoe', 'Willem' from fs.film where title = 'Platoon';
insert into fs.star(film, last, first)
select id, 'Berenger', 'Tom' from fs.film where title = 'Platoon';
insert into fs.film(title, year) values ('Wall Street', 1987);
insert into fs.star(film, last, first)
select id, 'Douglas', 'Michael' from fs.film where title = 'Wall Street';
insert into fs.star(film, last, first)
select id, 'Sheen', 'Charlie' from fs.film where title = 'Wall Street';
insert into fs.star(film, last, first)
select id, 'Hannah', 'Daryl' from fs.film where title = 'Wall Street';
insert into fs.star(film, last, first)
select id, 'Sheen', 'Martin' from fs.film where title = 'Wall Street';
* Re: Existential quantifier
2009-10-09 22:53 Existential quantifier Dag-Erling Smørgrav <des@des.no>
@ 2009-10-09 23:06 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2009-10-09 23:54 ` Re: Existential quantifier Dag-Erling Smørgrav <des@des.no>
1 sibling, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2009-10-09 23:06 UTC (permalink / raw)
To: Dag-Erling Smørgrav <des@des.no>; +Cc: pgsql-sql
On Sat, 10 Oct 2009, [utf-8] Dag-Erling Smørgrav wrote:
> Consider the attached schema (filmstars.sql), which is a poorly designed
> database of films and actors. The following query gives me a list of
> films in which either Charlie or Martin Sheen starred:
>
> select fs.film.title, fs.film.year
> from fs.film left join fs.star on fs.film.id = fs.star.film
> where fs.star.last = 'Sheen'
> group by fs.film.title, fs.film.year;
>
> Is there a way to do this without the "group by" clause?
Not at all tested as I don't have access to my db right now, but I think
something like one of these would work:
select fs.film.title, fs.film.year
from fs.film
where exists(select 1 from fs.star where fs.film.id = fs.star.film
and fs.star.last = 'Sheen');
select fs.film.title, fs.film.year
from fs.film
where fs.film.id in (select fs.star.film where fs.star.last = 'Sheen');
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Existential quantifier
2009-10-09 22:53 Existential quantifier Dag-Erling Smørgrav <des@des.no>
2009-10-09 23:06 ` Re: Existential quantifier Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2009-10-09 23:54 ` Dag-Erling Smørgrav <des@des.no>
2009-10-10 00:26 ` Re: Existential quantifier Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 6+ messages in thread
From: Dag-Erling Smørgrav @ 2009-10-09 23:54 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> Not at all tested as I don't have access to my db right now, but I think
> something like one of these would work:
>
> select fs.film.title, fs.film.year
> from fs.film
> where exists(select 1 from fs.star where fs.film.id = fs.star.film
> and fs.star.last = 'Sheen');
Ah, that was exactly what I was looking for.
> select fs.film.title, fs.film.year
> from fs.film
> where fs.film.id in (select fs.star.film where fs.star.last = 'Sheen');
ITYM
select fs.film.title, fs.film.year
from fs.film
where fs.film.id in (
select fs.star.film from fs.star where fs.star.last = 'Sheen'
);
(missing FROM)
DES
--
Dag-Erling Smørgrav - des@des.no
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Existential quantifier
2009-10-09 22:53 Existential quantifier Dag-Erling Smørgrav <des@des.no>
2009-10-09 23:06 ` Re: Existential quantifier Stephan Szabo <sszabo@megazone.bigpanda.com>
2009-10-09 23:54 ` Re: Existential quantifier Dag-Erling Smørgrav <des@des.no>
@ 2009-10-10 00:26 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2009-10-10 00:26 UTC (permalink / raw)
To: Dag-Erling Smørgrav <des@des.no>; +Cc: pgsql-sql
On Sat, 10 Oct 2009, [utf-8] Dag-Erling Smørgrav wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > Not at all tested as I don't have access to my db right now, but I think
> > something like one of these would work:
> >
> > select fs.film.title, fs.film.year
> > from fs.film
> > where exists(select 1 from fs.star where fs.film.id = fs.star.film
> > and fs.star.last = 'Sheen');
>
> Ah, that was exactly what I was looking for.
>
> > select fs.film.title, fs.film.year
> > from fs.film
> > where fs.film.id in (select fs.star.film where fs.star.last = 'Sheen');
>
> ITYM
>
> select fs.film.title, fs.film.year
> from fs.film
> where fs.film.id in (
> select fs.star.film from fs.star where fs.star.last = 'Sheen'
> );
>
> (missing FROM)
Yeah, that'd be necessary. You might want to try them on a realistic data
set to see how the various options are planned.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Existential quantifier
2009-10-09 22:53 Existential quantifier Dag-Erling Smørgrav <des@des.no>
@ 2009-10-09 23:11 ` Richard Albright <ralbright@insiderscore.com>
2009-10-09 23:48 ` Re: Existential quantifier Dag-Erling Smørgrav <des@des.no>
1 sibling, 1 reply; 6+ messages in thread
From: Richard Albright @ 2009-10-09 23:11 UTC (permalink / raw)
To: Dag-Erling Smørgrav <des@des.no>; +Cc: pgsql-sql
you could use distinct on
select distinct on (fs.film.title, fs.film.year ) title, year
from fs.film left join fs.star on fs.film.id = fs.star.film
where fs.star.last = 'Sheen';
On Sat, 2009-10-10 at 00:53 +0200, Dag-Erling Smørgrav wrote:
> Consider the attached schema (filmstars.sql), which is a poorly designed
> database of films and actors. The following query gives me a list of
> films in which either Charlie or Martin Sheen starred:
>
> select fs.film.title, fs.film.year
> from fs.film left join fs.star on fs.film.id = fs.star.film
> where fs.star.last = 'Sheen'
> group by fs.film.title, fs.film.year;
>
> Is there a way to do this without the "group by" clause?
>
> DES
> plain text document attachment (filmstars.sql)
> drop schema fs cascade;
>
> create schema fs;
>
> create table fs.film (
> id serial not null primary key,
> title varchar not null unique,
> year integer not null
> );
>
> create table fs.star (
> id serial not null primary key,
> film integer not null references fs.film(id),
> last varchar not null,
> first varchar not null,
> unique (film, last, first)
> );
>
> insert into fs.film(title, year) values ('Apocalypse Now', 1979);
> insert into fs.star(film, last, first)
> select id, 'Sheen', 'Martin' from fs.film where title = 'Apocalypse Now';
> insert into fs.star(film, last, first)
> select id, 'Brando', 'Marlon' from fs.film where title = 'Apocalypse Now';
> insert into fs.star(film, last, first)
> select id, 'Duvall', 'Robert' from fs.film where title = 'Apocalypse Now';
> insert into fs.star(film, last, first)
> select id, 'Ford', 'Harrison' from fs.film where title = 'Apocalypse Now';
>
> insert into fs.film(title, year) values ('Blade Runner', 1982);
> insert into fs.star(film, last, first)
> select id, 'Ford', 'Harrison' from fs.film where title = 'Blade Runner';
> insert into fs.star(film, last, first)
> select id, 'Young', 'Sean' from fs.film where title = 'Blade Runner';
> insert into fs.star(film, last, first)
> select id, 'Hauer', 'Rutger' from fs.film where title = 'Blade Runner';
> insert into fs.star(film, last, first)
> select id, 'Hannah', 'Daryl' from fs.film where title = 'Blade Runner';
>
> insert into fs.film(title, year) values ('Platoon', 1986);
> insert into fs.star(film, last, first)
> select id, 'Sheen', 'Charlie' from fs.film where title = 'Platoon';
> insert into fs.star(film, last, first)
> select id, 'Dafoe', 'Willem' from fs.film where title = 'Platoon';
> insert into fs.star(film, last, first)
> select id, 'Berenger', 'Tom' from fs.film where title = 'Platoon';
>
> insert into fs.film(title, year) values ('Wall Street', 1987);
> insert into fs.star(film, last, first)
> select id, 'Douglas', 'Michael' from fs.film where title = 'Wall Street';
> insert into fs.star(film, last, first)
> select id, 'Sheen', 'Charlie' from fs.film where title = 'Wall Street';
> insert into fs.star(film, last, first)
> select id, 'Hannah', 'Daryl' from fs.film where title = 'Wall Street';
> insert into fs.star(film, last, first)
> select id, 'Sheen', 'Martin' from fs.film where title = 'Wall Street';
--
Rick Albright
Senior Quantitative Analyst
Insiderscore LLC
ralbright@insiderscore.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Existential quantifier
2009-10-09 22:53 Existential quantifier Dag-Erling Smørgrav <des@des.no>
2009-10-09 23:11 ` Re: Existential quantifier Richard Albright <ralbright@insiderscore.com>
@ 2009-10-09 23:48 ` Dag-Erling Smørgrav <des@des.no>
0 siblings, 0 replies; 6+ messages in thread
From: Dag-Erling Smørgrav @ 2009-10-09 23:48 UTC (permalink / raw)
To: Richard Albright <ralbright@insiderscore.com>; +Cc: pgsql-sql
Richard Albright <ralbright@insiderscore.com> writes:
> you could use distinct on
>
> select distinct on (fs.film.title, fs.film.year ) title, year
> from fs.film left join fs.star on fs.film.id = fs.star.film
> where fs.star.last = 'Sheen';
Thanks, I didn't know about "distinct on". This version uses the
primary (surrogage) key:
select distinct on (fs.film.id) fs.film.title, fs.film.year
from fs.film left join fs.star on fs.film.id = fs.star.film
where fs.star.last = 'Sheen';
DES
--
Dag-Erling Smørgrav - des@des.no
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2009-10-10 00:26 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-10-09 22:53 Existential quantifier Dag-Erling Smørgrav <des@des.no>
2009-10-09 23:06 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2009-10-09 23:54 ` Dag-Erling Smørgrav <des@des.no>
2009-10-10 00:26 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2009-10-09 23:11 ` Richard Albright <ralbright@insiderscore.com>
2009-10-09 23:48 ` Dag-Erling Smørgrav <des@des.no>
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