Re: Subquery help with Max !

April

As an alternative to Guido's solution, you should consider analytic functions. These enable you to compute values such as those you require VERY easily and more importantly, efficiently. I've added two examples below. The first gets you your min year and month for key 1 specifically. The second gives you the min year and month for every distinct key in the table.

Note that Guido's version used FOUR t1 table lookups using the key = 1 and three sub-queries. The analytic function version uses ONE table lookup. Notice also that these functions do not use GROUP BY to rollup data so are very efficient.

--
-- Example one: WHERE key = 1
--
SELECT a.key, a.year, a.month
FROM  (SELECT key


       ,      MIN(year) OVER

                 (PARTITION BY key
                  ORDER BY year) AS year      


       ,      MIN(month) OVER

                 (PARTITION BY key
                  ORDER BY year,month) AS month


       ,      ROW_NUMBER() OVER

                        (PARTITION BY key
                         ORDER BY year) AS rn
       FROM   t1
       WHERE  key = 1) a
WHERE  rn = 1;

       KEY       YEAR      MONTH
---------- ---------- ----------
         1       2000          2

--
-- Example 2 : All keys
--
SELECT a.key, a.year, a.month
FROM  (SELECT key


       ,      MIN(year) OVER

                 (PARTITION BY key
                  ORDER BY year) AS year


       ,      MIN(month) OVER

                 (PARTITION BY key
                  ORDER BY year,month) AS month


       ,      ROW_NUMBER() OVER

                        (PARTITION BY key
                         ORDER BY year) AS rn
       FROM   t1) a
WHERE  rn = 1;

       KEY       YEAR      MONTH
---------- ---------- ----------
         1       2000          2
         2       2003          1
         3       2002          3

Good luck !

Regards

Adrian