Re: !! Help with query: Where Date = MAX(Date)
On Fri, 2005-02-04 at 09:19, Graham Cossey wrote: > Could someone please offer a little help. > > I have a table like: > > Year, Month, Start_date > 20041020041102 > 20041120041203 > 20041220050104 > 20050120050204 > 20050220050303 > > I need to get the latest Year,Month for a given date, so for example today > (20050204) I should retrieve 2005,01. > > As I'm using 4.0.20 I can't use subqueries so how can I create a query that > does this? > > SELECT year, month > FROM `dc_months` > WHERE start_date = (SELECT MAX(start_date) > from dc_months > where start_date <= '20050204') > > Any help much appreciated > > Graham I think this conveys the idea: SELECT year, month FROM `dc_months` WHERE start_date <= '20050204' ORDER BY start_date DESC LIMIT 1 -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: !! Help with query: Where Date = MAX(Date)
> > As I'm using 4.0.20 I can't use subqueries so how can I create > a query that > > does this? > > > > SELECT year, month > > FROM `dc_months` > > WHERE start_date = (SELECT MAX(start_date) > > from dc_months > > where start_date <= '20050204') > > > > Any help much appreciated > > > > Graham > > > > > > have a look here : > http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html > it should be useful for you. > Thanks Philippe that could do it. Graham. > -- > Philippe Poelvoorde > COS Trading Ltd. > +44.(0)20.7376.2401 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: !! Help with query: Where Date = MAX(Date)
Graham Cossey wrote: Could someone please offer a little help. I have a table like: Year, Month, Start_date 20041020041102 20041120041203 20041220050104 20050120050204 20050220050303 I need to get the latest Year,Month for a given date, so for example today (20050204) I should retrieve 2005,01. As I'm using 4.0.20 I can't use subqueries so how can I create a query that does this? SELECT year, month FROM `dc_months` WHERE start_date = (SELECT MAX(start_date) from dc_months where start_date <= '20050204') Any help much appreciated Graham have a look here : http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html it should be useful for you. -- Philippe Poelvoorde COS Trading Ltd. +44.(0)20.7376.2401 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]