Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: easy query (?)
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.7428Received on Fri Mar 07 1997 - 00:00:00 CST