Re: SQL query question

John Cunningham (shortseller12_at_yahoo.com) wrote:
: I've been stuck trying to figure this out. Any help appreciated.

: I've got a table (RATES) that looks like this

: City Rate Efct_date
: -----------------------
: NYC 100 19-DEC-2002
: NYC 110 03-MAR-2002
: CHI 75 19-DEC-2002

: What I want is the most recent rate for each city.

: I tried the following

: SELECT city, rate, max(efct_date)
: FROM rates
: GROUP BY city

: It doesn't like this because rate is not in the group by expression.
: But if I do put it in the group by expression, I get all the rates
: instead of just the most recent. This is probably so simple I just
: can't figure it out!! This is on an Oracle 8.0.6 server.

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
	  )