[Courses] [Postgresql] What is PostgreSQL?

[Cynthia Kiser]

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