Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: easy query (?)

Re: easy query (?)

From: Jerry Apfelbaum <japfelba_at_ican.ca>
Date: 1997/03/07
Message-ID: <3320983C.6EC5@ican.ca>#1/1

Alice Erickson wrote:
> I am trying to do what should be a simple query but doesn't seem to
> be so in SQL. Can someone give me a hint?
>
> I would like the final temperature reading for each city at the end of the
> day.
>
> EX.
> City Time Temp
> Boston 15:00 30
> Boston 24:00 10
> Phoenix 5:00 70
> Phoenix 6:00 72
>
> The resulting set should be
> Boston 24:00 10
> Phoenix 6:00 72
>
> but oracle will now allow you to use
> select city, max(temp), time from table
> group by city
> (as you are now also getting an aggregate of time)

You might try this:

SELECT city, time, temp
FROM table
WHERE (city, time) IN
( SELECT city, MAX(time)
  FROM table
  GROUP BY city
);

You may need to pay attention to the Time column if it is not a DATE field. You may run into confusion if it is a VARCHAR2 field (then '6:00' would come after '10:00').

Hope this helps.

-- 
============================================
Jerry Apfelbaum             japfelba_at_ican.ca
Eastern Sun Group Inc.   phone: 416.769.8738
Toronto, Canada           fax:  416.769.7428
Received on Fri Mar 07 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US