Re: Subquery help with Max !

oops, order by in an inline view was not allowed in 7.3.4!

I don't have 7.3.x anymore (you should upgrade as well, since it's unsupported by now), so I didn't test it...

So maybe something like:

select to_char(pdate, 'yyyy') paymentyear, to_char(pdate, 'mm') paymentmonth, c.* from
(select min(to_date(paymentyear||'-'|| paymentmonth, 'yyyy-mm')) pdate, bidno
from tblPaymentSchedule a, tblPaymentItems b where a.paymentkey = b.paymentkey
and a.paymentamount is not null
group by bidno
) t1, tblProject c
where t1.bidno = c.bidno
and c.bidno = 32144

or

select substr(pdate, 1, 4) paymentyear, substr(pdate, 6,2) paymentmonth, c.* from
(select min(paymentyear||'-'|| lpad(paymentmonth, 2, '0')) pdate, bidno from tblPaymentSchedule a, tblPaymentItems b where a.paymentkey = b.paymentkey
and a.paymentamount is not null
and b.bidno = 32144
group by b.bidno
) t1, tblProject c
where t1.bidno = c.bidno

might help ...

"Dieter Buecherl" <Dieter.Buecherl_at_t-online.de> schrieb im Newsbeitrag news:a5h6nh$gnn$01$1_at_news.t-online.com...
> 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 - 19:52:16 CST