Re: SQL query question

From: Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl>
Date: Tue, 15 Apr 2003 23:23:46 +0200
Message-ID: <36oo9v4cnmb591j48p5o6g0pd4a8fqe3bl@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 - 16:23:46 CDT