Max function in Query

T

tarweesh

hi,
let say i have a database of temperature reading or whatever.
i have a table t1 with fields (city_id, city_name, ...) and some other
info about the city.
and another table t2 (city_id, temperature, date) and i can have
several entries for each city.
i want to make a query that shows all the fields of t1 and the latest
temperature, i think one way to do this is to order t2 by date in a
descending order then select the first appearance of every city but i
failed to get the correct sql to do so.
so please if anybody knows how to do it this way or in any other way
this would be great.
 
J

John Nurick

Here's one that does a similar job on the Northwind sample database. It
returns all fields from Customers along with the OrderID and OrderDate
for the most recent order.

SELECT Customers.*, A.OrderID, A.OrderDate
FROM Customers INNER JOIN Orders AS A
ON Customers.CustomerID = A.CustomerID
WHERE A.OrderDate = (
SELECT MAX(B.ORDERDATE) FROM Orders AS B
WHERE B.CustomerID = A.CustomerID
)
ORDER BY Customers.CustomerID
;
 
D

David F Cox

someting along the lines of:-
SELECT citytemp.idCity, Max(citytemp.readingdate) AS MaxOfreadingdate
FROM citytemp
GROUP BY citytemp.idCity;

which finds the latest reading date

and

SELECT citytemp.idCity, TCity.info, citytemp.readingdate, citytemp.temp
FROM (citytemp INNER JOIN TCity ON citytemp.idCity = TCity.id_city) INNER
JOIN qlatestcityread ON (citytemp.readingdate =
qlatestcityread.MaxOfreadingdate) AND (citytemp.idCity =
qlatestcityread.idCity);

which gatehers all the city info and temperature reading, but only matches
on the maximum date i.e. the latest reading.
 
Top