Another solution is to use dense_rank if you have Oracle 8i upwards.
select city, rate, efct_date from
(
select city, rate, efct_date, dense_rank() over(partition by City order by
Efct_date desc) as date_rank
from rates)
where date_rank = 1;
"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message
news:36oo9v4cnmb591j48p5o6g0pd4a8fqe3bl_at_4ax.com...
> On 15 Apr 2003 11:49:14 -0800, yf110_at_vtn1.victoria.tc.ca (Malcolm
> Dew-Jones) wrote:
>
> >Two queries. First gets the right city/date combo, and second uses that
> >information to "index" into the whole table to get the complete data.
> >
> >untested
> >
> > SELECT *
> > FROM rates R
> > WHERE
> > R.CITY||'xxx'||R.efct_date IN
> > ( SELECT I.city || 'xxx' || max(I.efct_date)
> > FROM rates I
> > GROUP BY I.city
> > )
>
>
> awful solution which also won't perform in a real world situation
>
> Better
> select *
> from rates r
> where efct_date =
> (select max(efct_date)
> from rates r1
> where r1.city = r.city
> )
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Tue Apr 15 2003 - 17:36:05 CDT
