SQL: How To Find The Top String Column Value in a Grouped Query

Question

When grouping is there a simple way to get the first or top value out of a column. In the following example I want the last value in [Text] if sorted by [Date] descending.

SELECT 
   [Id],
   MAX([Date]),
   TOP 1 [Text],  <-- How do I do do this
   COUNT(*)
FROM
   [ExampleTable]
GROUP BY [Id]
ORDER BY [Date] DESC

Thanks in advance.

Answer 1

If you want the text corresponding to that [Id] & that [MaxDate], you can do this:

SELECT T2.*, T1.Text
FROM [ExampleTable] T1
     JOIN (SELECT 
               [Id],
               MAX([Date]) MaxDate,
               COUNT(*) Cnt
             FROM
               [ExampleTable]
         GROUP BY [Id]) T2
     ON T1.Id = T2.Id AND T1.Date = T2.MaxDate
ORDER BY [Date] DESC

if [Id] & [Date] form a primary key you will have a single row per (Id, Date) other wise many. If you really want one, either you put other constraints or use MAX or MIN::

SELECT T2.*, MAX(T1.Text) Text -- or MIN(T1.Text)
FROM [ExampleTable] T1
     JOIN (SELECT 
               [Id],
               MAX([Date]) MaxDate,
               COUNT(*) Cnt
             FROM
               [ExampleTable]
         GROUP BY [Id]) T2
     ON T1.Id = T2.Id AND T1.Date = T2.MaxDate
GROUP BY T2.*
ORDER BY [Date] DESC

If you want just a value for Text you can use MAX or MIN:

SELECT 
   [Id],
   MAX([Date]),
   MAX([Text]),
   COUNT(*)
FROM
   [ExampleTable]
GROUP BY [Id]
ORDER BY [Date] DESC

Answer 2

If i understand you correctly, there can be multiple dates for any given id, and multiple text for a given date.

If you use Sql Server 2005

I got this to work

DECLARE @Table TABLE(
     ID INT,
     DDATE DATETIME,
     VAL VARCHAR(MAX)
)

INSERT INTO @Table (ID,DDATE,VAL) SELECT 1, '01 Jan 2009', '1'
INSERT INTO @Table (ID,DDATE,VAL) SELECT 1, '01 Feb 2009', '2'
INSERT INTO @Table (ID,DDATE,VAL) SELECT 1, '01 Feb 2009', '3'
INSERT INTO @Table (ID,DDATE,VAL) SELECT 2, '01 Jan 2009', '4'


SELECT  ID,
     DDATE,
     VAL,
     ROWNUMBER
FROM    (
      SELECT t.ID,
        t.DDATE,
        t.VAL,
        ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY DDATE) ROWNUMBER
      FROM @Table t INNER JOIN
        (
         SELECT ID,
           MAX(DDATE) AS MAXDATE
         FROM @Table
         GROUP BY ID
        ) md ON t.ID = md.ID
          AND t.DDATE = md.MAXDATE
     ) RowNumber
WHERE   RowNumber.ROWNUMBER = 1