Postgres Pro
Downloads
Home   >   mailing lists

Thread: Can this be done in one query?

Can this be done in one query?

From
Randall Perry
Date:
With the following:

    select max(date) from log order by date_time desc limit 12;

The limit clause has no effect on the results; it'll get the latest date
from the entire record set rather than from the last 12 records.

It works if I do this:
   create temp table as select date from log order by dat_time desc limit
12;

    select max(date) from temp;

So, is there a way to do this in one query without creating a temp table?

--
Randy Perry
sysTame
Mac Consulting/Sales

phn                 772.589.6449
mobile email        help@systame.com

Re: Can this be done in one query?

From
"Bjoern Metzdorf"
Date:
> So, is there a way to do this in one query without creating a temp table?

How about a view:

create view test as select max(date) from log order by date_time desc limit
12;

select max(date) from test;

Greetings,
Bjoern

Re: Can this be done in one query?

From
Stephan Szabo
Date:
On Tue, 7 May 2002, Randall Perry wrote:

> With the following:
>
>     select max(date) from log order by date_time desc limit 12;
>
> The limit clause has no effect on the results; it'll get the latest date
> from the entire record set rather than from the last 12 records.

Maybe something like:
select max(date) from (select date from log order by date_time desc limit
12) c;

Re: Can this be done in one query?

From
Jean-Luc Lachance
Date:
Or maybe if date_time is of type timestamp:

select date( select date_time from log order by date_time desc limit 1);

JLL

Stephan Szabo wrote:
>
> On Tue, 7 May 2002, Randall Perry wrote:
>
> > With the following:
> >
> >     select max(date) from log order by date_time desc limit 12;
> >
> > The limit clause has no effect on the results; it'll get the latest date
> > from the entire record set rather than from the last 12 records.
>
> Maybe something like:
> select max(date) from (select date from log order by date_time desc limit
> 12) c;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

Re: Can this be done in one query?

From
Randall Perry
Date:
Thanks, that's the best solution.

But I don't get the syntax; what's the 'c' at the end -- an alias?

>
> On Tue, 7 May 2002, Randall Perry wrote:
>
>> With the following:
>>
>>     select max(date) from log order by date_time desc limit 12;
>>
>> The limit clause has no effect on the results; it'll get the latest date
>> from the entire record set rather than from the last 12 records.
>
> Maybe something like:
> select max(date) from (select date from log order by date_time desc limit
> 12) c;
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Randy Perry
sysTame
Mac Consulting/Sales

phn                 772.589.6449
mobile email        help@systame.com

Re: Can this be done in one query?

From
Randall Perry
Date:

>
> On Tue, 7 May 2002, Randall Perry wrote:
>
>> With the following:
>>
>>     select max(date) from log order by date_time desc limit 12;
>>
>> The limit clause has no effect on the results; it'll get the latest date
>> from the entire record set rather than from the last 12 records.
>
> Maybe something like:
> select max(date) from (select date from log order by date_time desc limit
> 12) c;

Thanks, that did it.


>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Randy Perry
sysTame
Mac Consulting/Sales

phn                 772.589.6449
mobile email        help@systame.com