sql help, reusing a column

From: Andy Colson <andy(at)squeakycode(dot)net>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: sql help, reusing a column
Date: 2010-04-29 21:51:50
Message-ID: 4BD9FF76.2010200@squeakycode.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Here is my query, which works:

select organization,
state,
(select max(idate) from times where customers.custid=times.custid and
taskid = 27) as lastdate,
age( (select max(idate) from times where
customers.custid=times.custid and taskid = 27) )
from customers
order by lastdate desc nulls last;

I'd love to use age(lastdate) instead of age( (repeat sql) ), but it
does not seem to work.

I tried this:

select organization, state, max(idate), age(max(idate))
from customers
inner join times using(custid)
where taskid = 27
group by organization, state
order by idate desc nulls last;

but get error that times.idate must appear in group by or used in agg
func... except it is used in an agg func.

Any hints on what I'm missing?

Thanks,

-Andy

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andy Colson 2010-04-29 22:02:26 Re: sql help, reusing a column
Previous Message Simon Riggs 2010-04-29 21:44:12 Re: Recovering Data from a crashed database