List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 2 1999 9:45am
Subject:Re: [SQL] aggregation functions (max)
View as plain text  
On Mo, 1999-08-02 11:02:52 +0200, Christian Bruno wrote:
> the problem is :
> to get the most recent record for a PRODUCT, i need 2 select :
> 
> :: select max(CREATIONDATE) from PRODUCTS where CODE='searched_code'
> :: select CODE,NAME from PRODUCTS where CODE='searched_code' and
> creationdate='date_from_previous_select'

A work-around for this problem is to sort descending by the field you
want to be maximized and then only fetch the first resulting row
(which holds this maximum):

  SELECT   code, name, creationdate
  FROM     PRODUCTS
  WHERE    code='searched_code'
  ORDER BY creationdate DESC;

An SQL extension specific to MySQL is the LIMIT clause, which enables
you to request only part of the resulting rows.  So a better solution
should be:

  SELECT   code, name, creationdate
  FROM     PRODUCTS
  WHERE    code='searched_code'
  ORDER BY creationdate DESC
  LIMIT    1;

This should be quite efficient. (As would be your version using
sub-select by the way, but these aren't supported by MySQl, yet).


I found another method, too, but it is quite inefficient, so I'll put
it in a seperate answer hoping that nobody will associate it with the
reasonable answer given here. :)


> ps: please excuse my English, it is still in Beta-Test

Ah come on, your English is ways better than my French ...
(Though I'm probably better at German. ;-)

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
[SQL] aggregation functions (max)Christian Bruno2 Aug
  • Re: [SQL] aggregation functions (max)Martin Ramsch2 Aug
    • Re: [SQL] aggregation functions (max)Martin Ramsch2 Aug