SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo]. [Temperature] (
[ID] [int] IDENTITY (*) Not NULL,
[Month] [INT] Null
[Year] [INT] Null
[Temperature] [INT] Null
CONSTRAINT [pk_temperature] PRIMARY KEY CLUSTERED
(
[ID] ASC
) with (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, allow_page_locks = ON) O N [PRIMARY]
) on [PRIMARY]
GO
--the highest monthly temperature of two per month (version 1)
Select T.* from (
SELECT [Temperature],[year],[month],row_number () over (partition by [year],[month] order BY [temperature] desc) RowNumber from [temperature]
) T where t.rownumber<=2
--the highest monthly temperature of two per month (version 2)
select * FROM [temperature] a WHERE (
Select COUNT (*) from [temperature] b where a.year=b.year and A.month=b.month and A.temperature<b.temperature
) <2 ORDER BY A.year,a.month
SQL: The highest two temperatures per month