Finding first record that meets conditions + allowing grouping

Question

Finding first record that meets conditions + allowing grouping

SSCommitted

Points: 1720

August 31, 2022 at 5:07 pm

Just want to explore the best way to pick the "first" record from a table that meets certain conditions, while at the same time allowing grouping to occur.

As one example, consider a "planting" scenario where one needs to know the first month it is safe to plant seeds in different cities. Assume the temperature needs to be at least 64 degrees. Here is some temperature data by city:

-- Drop Tables If Exist
DROP TABLE IF EXISTS dbo.CityTemperature;
DROP TABLE IF EXISTS dbo.City;
DROP TABLE IF EXISTS dbo.[Month];

-- "Month" Table
CREATE TABLE dbo.[Month]
(
MonthID     INT NOT NULL PRIMARY KEY,
[MonthName] VARCHAR(32)
);

INSERT INTO dbo.[Month]([MonthID], [MonthName])
      SELECT 4 , 'Apr'
UNION SELECT 5 , 'May'
UNION SELECT 6 , 'Jun'
UNION SELECT 7 , 'Jul';
 

-- "City" Table
CREATE TABLE dbo.City
(
CityID   INT NOT NULL PRIMARY KEY,
CityName VARCHAR(50)
);

INSERT INTO dbo.City(CityID, CityName) VALUES(1, 'New York City');
INSERT INTO dbo.City(CityID, CityName) VALUES(2, 'Miami');
INSERT INTO dbo.City(CityID, CityName) VALUES(3, 'Minneapolis');

-- "CityTemperature" Table
CREATE TABLE dbo.CityTemperature
(
TempID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
CityID INT NOT NULL 
REFERENCES dbo.City(CityID),
MonthID INT NOT NULL 
REFERENCES dbo.[Month](MonthID),
LowTemp INT,
HiTemp  INT
);

-- New York City
INSERT INTO dbo.CityTemperature(CityID, MonthID, LowTemp, HiTemp) 
SELECT 1, 4, 45, 64
INSERT INTO dbo.CityTemperature(CityID, MonthID, LowTemp, HiTemp) 
SELECT 1, 5, 54, 72
INSERT INTO dbo.CityTemperature(CityID, MonthID, LowTemp, HiTemp) 
SELECT 1, 6, 64, 80
INSERT INTO dbo.CityTemperature(CityID, MonthID, LowTemp, HiTemp) 
SELECT 1, 7, 69, 84
 
-- Miami
INSERT INTO dbo.CityTemperature(CityID, MonthID, LowTemp, HiTemp) 
SELECT 2, 4, 70, 80
INSERT INTO dbo.CityTemperature(CityID, MonthID, LowTemp, HiTemp) 
SELECT 2, 5, 74, 73
INSERT INTO dbo.CityTemperature(CityID, MonthID, LowTemp, HiTemp) 
SELECT 2, 6, 77, 86
INSERT INTO dbo.CityTemperature(CityID, MonthID, LowTemp, HiTemp) 
SELECT 2, 7, 78, 87

-- Minneapolis
INSERT INTO dbo.CityTemperature(CityID, MonthID, LowTemp, HiTemp) 
SELECT 3, 4, 37, 55
INSERT INTO dbo.CityTemperature(CityID, MonthID, LowTemp, HiTemp) 
SELECT 3, 5, 49, 68
INSERT INTO dbo.CityTemperature(CityID, MonthID, LowTemp, HiTemp) 
SELECT 3, 6, 59, 77
INSERT INTO dbo.CityTemperature(CityID, MonthID, LowTemp, HiTemp) 
SELECT 3, 7, 64, 82

In order to determine the first month where the low temp is at least 64 degrees for each city, I came up with the query below. But it's kind of kludgy. Is there a better / easier way?

-- Find First Month In Each City With a Low Temperature of at Least 64 Degrees
SELECT M.[MonthName], Y.CityName
FROM 
(
SELECT MIN(X.MonthID) AS MonthID, X.CityName
FROM
(
SELECT C.CityName, CT.MonthID
FROM dbo.CityTemperature CT
INNER JOIN dbo.City C ON C.CityID = CT.CityID
WHERE CT.LowTemp >= 64
) X 
GROUP BY X.CityName
) Y
INNER JOIN dbo.[Month] M ON M.MonthID = Y.MonthID

Results

Ideas?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Here's another way

WITH CTE AS (
 SELECT CityID, MonthID, LowTemp, HiTemp,ROW_NUMBER() OVER(PARTITION BY CityID ORDER BY MonthID) AS rn
 FROM dbo.CityTemperature
 WHERE LowTemp >= 64
)
SELECT m.MonthName, c.CityName
FROM CTE t
INNER JOIN dbo.City c ON c.CityID = t.CityID
INNER JOIN dbo.[Month] m ON m.MonthID = t.MonthID
WHERE t.rn = 1;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

Lol, that's exactly what I came up with:

;WITH cte_city_months AS (
    SELECT CT.CityID, CT.MonthID, ROW_NUMBER() OVER(PARTITION BY CT.CityID ORDER BY CT.MonthID) AS row_num
    FROM dbo.CityTemperature CT
    WHERE CT.LowTemp >= 64
    GROUP BY CT.CityID, CT.MonthID
)
SELECT M.MonthName, C.CityName
FROM cte_city_months ccm
INNER JOIN dbo.Month M ON M.MonthID = ccm.MonthID
INNER JOIN dbo.City C ON C.CityID = ccm.CityID
WHERE ccm.row_num = 1
ORDER BY CityName

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

John Jakob SSCommitted Points: 1720 More actions · Answer 3

John Jakob

SSCommitted

Points: 1720

August 31, 2022 at 6:19 pm

#4082563

Thanks guys!

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

Alternatively using OUTER or CROSS APPLY

SELECT c.CityName, x.MonthName
  FROM dbo.City c
 OUTER APPLY (SELECT TOP(1) m.MonthName
                FROM dbo.CityTemperature ct
               INNER JOIN dbo.Month m
                       ON m.MonthID = ct.MonthID
               WHERE ct.LowTemp >= 64
                 AND ct.CityID = c.CityId
               ORDER BY m.MonthID) x
 ORDER BY c.CityName;

Halvorsonsa Grasshopper Points: 11 More actions · Answer 5

I appreciate the information and advice you have shared. I will try to figure it out for more.

My eClass Parent Portal

This reply was modified 2 years, 2 months ago by Halvorsonsa.