Max function in Query

  • Thread starter Thread starter tarweesh
  • Start date Start date
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.
 
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
;
 
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.
 
Back
Top