Selecting data related to an aggregated column

• bartedgerton

  SSCrazy

  Points: 2062

  More actions

  October 12, 2017 at 12:42 pm

  #325800

  I'm commonly tasked with requests to produce data related to an aggregated column value, what some people call a "Knight Join" (as in the Chess move)   For instance, querying a history table, we want to know the date of the last modification for a group, and we also want to know the user who made the last modification.   I usually end up with a CTE that gets the MAX date for the group, then either a second CTE, or in the base query, I rejoin to the source table to get the columns related to the aggregated (MAX) column.  Here is an example that shows what I'm talking about.   My question is does anyone know if there is a simpler method to accomplish the same thing?

  CREATE TABLE #History (
 ID INT
, GROUPID INT
, LASTCHANGE DATETIME
, LASTCHANGEUSER INT
);
GO

  INSERT INTO #History VALUES  (1,1,'1980-01-01',50), (2,1,'1981-10-01',33), (3,3,'1983-10-01',20), (4,3,'1984-10-01',15), (5,3,'1985-10-01',50);GO

  INSERT INTO #History VALUES
  (1,1,'1980-01-01',50)
, (2,1,'1981-10-01',33)
, (3,3,'1983-10-01',20)
, (4,3,'1984-10-01',15)
, (5,3,'1985-10-01',50);
GO

  WITH a AS ( SELECT    GROUPID  , MAX(LASTCHANGE) AS MAXCHANGE FROM #History GROUP BY GROUPID)SELECT   h.GROUPID , h.LASTCHANGE , h.LASTCHANGEUSERFROM #History hJOIN a ON h.GROUPID = a.GROUPID AND h.LASTCHANGE = a.MAXCHANGE;GO

  WITH a AS (
 SELECT
    GROUPID
  , MAX(LASTCHANGE) AS MAXCHANGE
 FROM #History
 GROUP BY GROUPID
)
SELECT
   h.GROUPID
 , h.LASTCHANGE
 , h.LASTCHANGEUSER
FROM #History h
JOIN a ON h.GROUPID = a.GROUPID AND h.LASTCHANGE = a.MAXCHANGE;
GO

  GROUPID LASTCHANGE LASTCHANGEUSER
  3 1985-10-01 00:00:00.000 50
  1 1981-10-01 00:00:00.000 33

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  October 12, 2017 at 1:28 pm

  #1963463

  It's difficult to tell from this small a sample of data, but here's a couple of options, depending on how many rows per GROUPID you have in your history table one way may work better than the other.  For either of these, you'll probably want to consider an index on your history table.  Your original query seems to produce a better plan without the index though:
  CREATE NONCLUSTERED INDEX IX_History_GROUPID_Recentness ON #History(GROUPID, LASTCHANGE DESC) INCLUDE (LASTCHANGEUSER);

  --ROW_NUMBER()
WITH ranked AS (
SELECT GROUPID, LASTCHANGE, LASTCHANGEUSER, ROW_NUMBER() OVER (PARTITION BY GROUPID ORDER BY LASTCHANGE DESC) AS recentness
FROM #History)
SELECT
 h.GROUPID
, h.LASTCHANGE
, h.LASTCHANGEUSER
FROM ranked h
WHERE h.recentness = 1;

  --CROSS APPLY
WITH grouplist AS (
  SELECT 1 AS GROUPID UNION ALL SELECT 3 AS GROUPID)
SELECT g.GROUPID, l.LASTCHANGE, l.LASTCHANGEUSER
FROM grouplist g
CROSS APPLY
  (SELECT TOP 1 h.LASTCHANGE, h.LASTCHANGEUSER FROM #History h WHERE h.GROUPID = g.GROUPID ORDER BY LASTCHANGE DESC) l