Postgres Pro
Downloads
Home   >   mailing lists

Thread: date problem

date problem

From
tony
Date:
Hi,

I thought this would be a classic sort of query but google did no give
me the answer.

I am attempting to select records where one of the dates is the latest
date before today

select max(date) from expo where date < now()

works for one record but other fields I need must be in aggregate or
grouped. Is there a simple SQL request to get the most recent records
from a set of joined tables?

TIA

Tony

Re: date problem

From
Roman Neuhauser
Date:
# tony@tgds.net / 2005-05-16 09:48:08 +0200:
> I am attempting to select records where one of the dates is the latest
> date before today
>
> select max(date) from expo where date < now()
>
> works for one record but other fields I need must be in aggregate or
> grouped. Is there a simple SQL request to get the most recent records
> from a set of joined tables?

    select *
    from expo
    where date = (select max(date)
                  from expo
                  where date < now()) as x;

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991

Re: date problem

From
Richard Huxton
Date:
tony wrote:
> Hi,
>
> I thought this would be a classic sort of query but google did no give
> me the answer.
>
> I am attempting to select records where one of the dates is the latest
> date before today
>
> select max(date) from expo where date < now()
>
> works for one record but other fields I need must be in aggregate or
> grouped. Is there a simple SQL request to get the most recent records
> from a set of joined tables?

SELECT * FROM expo WHERE date = (SELECT max(date) FROM expo WHERE date <
now());

In fact, you might want to rewrite the subselect. Oh, and "date" is
likely to cause trouble as a column-name.

SELECT * FROM expo
WHERE my_date = (
    SELECT my_date FROM expo
    WHERE my_date < now()
    ORDER BY my_date DESC LIMIT 1
)


--
   Richard Huxton
   Archonet Ltd

Re: date problem

From
tony
Date:
Le lundi 16 mai 2005 à 10:30 +0200, Roman Neuhauser a écrit :

>     select *
>     from expo
>     where date = (select max(date)
>                   from expo
>                   where date < now()) as x;

Thanks!

I keep forgetting this for some strange reason... I was putting it in
the "from" instead of the "where"

Tony

Re: date problem

From
tony
Date:
Le lundi 16 mai 2005 à 09:44 +0100, Richard Huxton a écrit :

> > works for one record but other fields I need must be in aggregate or
> > grouped. Is there a simple SQL request to get the most recent records
> > from a set of joined tables?
>
> SELECT * FROM expo WHERE date = (SELECT max(date) FROM expo WHERE date <
> now());
>
> In fact, you might want to rewrite the subselect. Oh, and "date" is
> likely to cause trouble as a column-name.
>
> SELECT * FROM expo
> WHERE my_date = (
>     SELECT my_date FROM expo
>     WHERE my_date < now()
>     ORDER BY my_date DESC LIMIT 1
> )

Thanks! I wasn't really using date but for clarity had put that instead
of my French column name...

Tony