Greatest_N_Per_Group, Sql Server

Greatest_N_Per_Group

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

CREATE TABLE CitizenPowerLevel
    ("KeyID" int, "CitizenID" int, "RandomSystemID" int, "ParentID" int, "Quality" varchar(8), "Begin" DateTime, "End" DateTime, "Power" decimal(8,4))
;
    
INSERT INTO CitizenPowerLevel
    ("KeyID", "CitizenID", "RandomSystemID", "ParentID", "Quality", "Begin", "End", "Power")
VALUES
    (98554, 40, 0, 0, 'Mediocre', '2010-02-19 02:00:00', '2010-02-19 02:00:00', 3),
    (98632, 40, 11, 309, 'Special', '2010-10-05 19:21:00', '2010-10-05 19:21:00', 4.2),
    (98704, 40, 11, 322, 'Special', '2010-10-05 19:22:00', '2010-10-05 19:22:00', 5),
    (99208, 40, 11, 309, 'Special', '2011-02-15 19:56:00', '2011-02-15 19:56:00', 3.9),
    (99279, 40, 11, 322, 'Special', '2011-02-15 19:56:00', '2011-02-15 19:56:00', 6),
    (106337, 40, 11, 309, 'Special', '2015-02-27 15:25:00', '2015-02-27 15:25:00', 4.8),
    (106406, 40, 11, 322, 'Special', '2015-02-27 15:25:00', '2015-02-27 15:25:00', 5.4),
    (98545, 172, 0, 0, 'Mediocre', '2010-02-19 02:00:00', '2010-02-19 02:00:00', 0.5),
    (98622, 172, 11, 309, 'Special', '2010-10-05 19:21:00', '2010-10-05 19:21:00', 1.1),
    (98694, 172, 11, 322, 'Special', '2010-10-05 19:22:00', '2010-10-05 19:22:00', 1.3),
    (99340, 172, 11, 309, 'Special', '2011-04-29 20:08:00', '2011-04-29 20:08:00', 1.4),
    (99409, 172, 11, 322, 'Special', '2011-04-29 20:08:00', '2011-04-29 20:08:00', 1.02),
    (65923, 187, 0, 0, 'System', '2008-12-17 03:00:00', '2008-12-17 03:00:00', 1.3)
;

WITH CTE AS
(
	SELECT	*,
			RN = ROW_NUMBER() OVER(PARTITION BY CitizenID, ParentID 
								   ORDER BY [End] DESC)
	FROM citizenPowerLevel
    WHERE Quality = 'Special'
)
SELECT [Power], CitizenID, ParentID
FROM CTE
WHERE RN = 1;


run \| edit \| history \| help	0