Post reply

Selecting data related to an aggregated column

  • 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

Viewing post 1 (of 2 total)

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