Re: Subquery help with Max !

How about:

select * from
(select a.paymentyear y, a.paymentmonth m from tblPaymentSchedule a, tblPaymentItems b, tblProject c where a.paymentkey = b.paymentkey
and b.Bidno = c.Bidno
and c.Bidno = 32144
a.paymentamount is not null
order by 1,2 ) t2
where rownum = 1

HTH Dieter

"April" <privatebenjamin_at_hushmail.com> schrieb im Newsbeitrag news:21e9f79.0202261204.5925ce7e_at_posting.google.com...
> 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 - 17:46:33 CST