Thread: sql help, reusing a column

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

On 4/29/2010 4:51 PM, Andy Colson wrote:
> 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
>

Ahh, shoot, it was the idate in the order by, not the select list.  Both
"order by 3" and "order by max(idate)" work just fine.

Sorry for the noise... but still... I'm kinda curious, in my first
example, how you can re-use a column.  Is there a way to:

select organization, state,
  (select max(idate) from times where customers.custid=times.custid and
taskid = 27) as lastdate,
  age(lastdate)
from customers


-Andy

Andy Colson wrote on 29.04.2010 23:51:
> 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.

This should work:

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

On 04/29/2010 05:08 PM, Thomas Kellerer wrote:
> SELECT organization, state, lastdate, age(lastdate)
> FROM (
>    SELECT organization,
>           state,
>           (select max(idate) from times where
> customers.custid=times.custid and taskid = 27) as lastdate
>    FROM customers
> ) t
> order by lastdate desc

Ah, yes, that does work, very nice.  Thank you.

-Andy