Re: Subquery help with Max !

Thanks Adrian,
sorry, I should have mentioned that I am using Oracle 7.3.4. I don't believe those functions are available.

But you are right,
I have been trying to rewrite the query supplied by Guido because it is too slow. At first 5.5 minutes, then I added some indexes and now 30 secs.
Still 30 secs is a long time to wait for one row of data.

Would you be able to provide assistance in how to improve this query for Oracle 7.3.4?

I have tried the query below, but it actually returns the lowest month for each year, if there is more than one year. Please note the setup is different from the original posting as I am getting the key from another related table(tblProject).

ex. tblProject        tblPaymentItems         tblPaymentSchedule
    Bidno             BidNo  PaymentKey       PaymentKey Year  Month
    ------            -----   ---------       ---------- ----  -----
    32144             32144    7594            7594      2001   12
    32144             32144    7594            7594      2000    1
    32144             32144    7586            7586      2002    3
    32144             32144    7586            7586      2002    5  
             

SELECT a.paymentyear,a.paymentmonth
FROM tblProject, tblPaymentItems, tblpaymentschedule a WHERE tblProject.Bidno = tblPaymentItems.Bidno and tblPaymentItems.PAYMENTKEY= a.PAYMENTKEY AND tblProject.BidNo = 32144 and a.paymentamount is not null GROUP BY a.paymentyear,a.paymentmonth
HAVING a.paymentyear = min(a.paymentyear) AND a.paymentmonth =(SELECT min(b.paymentmonth)

		   FROM tblProject, tblPaymentItems, tblpaymentschedule b 
		   WHERE tblProject.Bidno = tblPaymentItems.Bidno 
		   AND tblPaymentItems.PAYMENTKEY = b.PAYMENTKEY
		   AND tblProject.BidNo = 32144 and b.paymentamount is not null
		   AND b.PaymentYear = a.PaymentYear)

This query will return  year   month
                        ----   -----
                        2000    1
                        2002    3

in 130 msecs, but it's not what I am looking for, which is 1 record of Year(2000) and Month(1) which is the lowest Month and Year for the BidNo = 32144.

Thanks very much
April    

billiauk_at_yahoo.co.uk (Adrian Billington) wrote in message news:<dee17a9f.0202260516.475885f5_at_posting.google.com>...
> 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
Received on Tue Feb 26 2002 - 14:04:13 CST