List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 2 1999 10:33am
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'

And I just wrote:
> I found another method, too, but it is quite inefficient, [...]

With a little trick it's possible to compute rank numbers and then
select on these, too, making it easy to fetch the first or last or
second of something.

The idea is to do a cross product over the table and then compare each
creationdate for a given code with all the other ones for this code,
counting these comparisons:

   SELECT   t1.code, t1.name, t1.creationdate,
            SUM(t1.creationdate<t2.creationdate) AS rank
   FROM     PRODUCTS as t1, PRODUCTS as t2
   WHERE    t1.code = t2.code
   GROUP BY t1.code, t1.creationdate;

A rank of 0 for a date means, that no other creationdate within the
group has been greater than this one , that is it's the maximum.

So to get the complete list of your current products, this should
work:

   SELECT   t1.code, t1.name, t1.creationdate
   FROM     PRODUCTS as t1, PRODUCTS as t2
   WHERE    t1.code = t2.code
   GROUP BY t1.code, t1.creationdate
   HAVING   SUM(t1.creationdate<t2.creationdate) = 0;

Or for a specific code:
   SELECT   t1.code, t1.name, t1.creationdate
   FROM     PRODUCTS as t1, PRODUCTS as t2
   WHERE    t1.code = t2.code
     AND    t1.code = 'searched_code'
   GROUP BY t1.code, t1.creationdate
   HAVING   SUM(t1.creationdate<t2.creationdate) = 0;


As a last word, PLEASE note that this is NOT efficient and is maybe
better done with two queries!

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