select records of highest date with combination of city
-
I have a table as below -
RId tmpDate City MaxTemp MinTemp
---- ------------- -------------- --------- ---------
1 2001-01-02 Patna 38.70 29.19
2 2001-01-02 Mumbai 38.70 29.19
3 2001-01-02 Delhi 38.70 29.19
4 2001-01-01 Delhi 38.70 29.19
5 2001-01-01 Mumbai 38.70 29.19
6 2001-01-03 Mumbai 38.70 29.19
7 2001-01-03 Delhi 38.70 29.19
8 2001-01-04 Mumbai 38.70 29.19
9 2001-01-04 Delhi 38.70 29.19
I want to select the latest temperature of all the cities. I mean to say my result should look like this -
RId tmpDate City MaxTemp MinTemp
---- ------------- -------------- --------- ---------
1 2001-01-02 Patna 38.70 29.19
8 2001-01-04 Mumbai 38.70 29.19
9 2001-01-04 Delhi 38.70 29.19
can somebody help me to write a query for this.
Thanx !!!!
-
Select * from dbo.YourTable Y inner join
(Select City, max(tmpDate) as tmpDate from dbo.yourTable group by City) dtMaxTmp on Y.City = dtMaxTmp.City and Y.tmpDate = dtMaxTmp.TmpDate
-
SELECT Outie.*
FROM dbo.YourTable Outie
INNER JOIN (SELECT MAX(RId) RId
FROM dbo.yourTable GROUP BY City) Innie
ON Outie.RId = Innie.RId
Is another way to skin the same cat. It uses the IDENTITY column (may be better performance)
Good Hunting!AJ Ahrens
webmaster@kritter.net -
That assumes that the lastest date has the max identity... this is assuming a lot in a real life environement
. -
Except for your usage of "lastest" remi - your thinking (comme d'habitude) is flawless!
**ASCII stupid question, get a stupid ANSI !!!** -
I wouldn't be mysefl without my usual typeaus
. -
typeau...chapeau...beau....l'eau...
Oops - it's not Friday is it ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
If only that were true
.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply