[Courses] [Postgresql] What is PostgreSQL?
Cynthia Kiser
cnk at ugcs.caltech.edu
Sat Nov 24 18:20:33 UTC 2007
Quoting Michelle Murrain <michelle at murrain.net>:
> > I also still miss 'first' as one of the group-by column options that
> > Access has. Does Postgres have something like that? Doesn't look like
> > it. I'll have to see if any of the new SQL extensions in Oracle now
> > let me do that.
>
> Just so you know, PG does have a "GROUP BY" clause. We'll get to some of
> that in the SQL section.
Yup. It has 'GROUP BY' and most of the standard aggregate functions -
min, max, sum, count, avg - even variance and standard deviation. Just
no 'first'. But since Postgres does support subqueries, I can get to
the rest of the values for the row that meets my criteria.
For a concreate example, 2 tables. One that contains addresses and one
that has the pending updates for addresses, addresses_pending.
I need to get the most recently updated row - no matter which
underlying table it comes from. And I need all the data from that row,
not just the fields that can be used to determine which rows belong
together.
addresses addresses_pending
address_id address_id
address_update_id address_update_id -- null in addresses
modified_date modified_date
belongs_to_id belongs_to_id
line1 line1
line2 line2
city city
state state
zip zip
create or replace view addresses_all as (
select * from addresses
union all
select * from addresses_pending
);
create or replace view addresses_current as (
select a.*
from address_all a,
(select address_id, address_update_id,
max(madified_date) as latest_update
from address_all
group by address_id, address_update_id) latest
where a.address_update_id = latest.address_update_id
and a.address_id = latest.address_id
and a.modified_date = latest.last_update);
(I didn't run this in pgsql, so there may be typos)
If Postgres had first and last (like Access's dfirst and dlast), then
I could have avoided the view on the fly and correlated subquery and
just done:
-- NOT LEGAL PG
select address_id, address_update_id,
max(madified_date) as latest_update,
first(belongs_to_id),
first(line1), first(line2), first(city),
first(state), first(zip)
from address_all
group by address_id, address_update_id
order by max(modified_date)
--
Cynthia N. Kiser
cnk at ugcs.caltech.edu
More information about the Courses
mailing list