<h1>Example</h1>
<p>There is an application that measures temperature in each town of the world. Each measurement is taken every 5 minutes and written in to the Measurement table.</p>
<pre><code>CREATE TABLE [dbo].[Measurement](
    [MeasurementID] [int] IDENTITY(1,1) NOT NULL,
    [Town] [varchar](50) NOT NULL,
    [Date] [datetime] NOT NULL,
    [Temp] [int] NOT NULL,
CONSTRAINT [PK_Measurement] PRIMARY KEY CLUSTERED 
(
    [MeasurementID] ASC
)) ON [PRIMARY]
</code></pre>
<h1>Question</h1>
<p>What is the most efficient query to get a list of the towns and their current temperature?</p>
<p>Assume there are 100k towns and 10 million records</p>
<p><em>NOTE: I have added a couple of possible answers, but there are probably other options.</em></p>

```
```
select s.*
from Measurement s
where exists ( 
   select 1
   from Measurement s1
   where s.Town = s1.Town
   group by s1.Town
   having max( s1.Date )= s.Date)
   order by s.Town
```
```