t-sql Aggregate Max

Question

Hi.

I have table:
CREATE TABLE [dbo].[test] (
  [name] nvarchar(max) NULL,
  [date] datetime NULL
)

And records on it:

a 2010-09-02 12:00:00 
a 2010-09-02 11:00:00 
b 2010-09-02 12:00:00 
b 2010-09-02 11:00:00

And i want to get all name with the newest date:

I may do:

select t.[name] from test t
group by t.[name]
having max(date) = (select MAX(DATE) from test where [name] = t.[name])

which have one problem - i can't get a date

I may do:

select t.* 
    from test t
    where t.[date] = (select MAX(DATE) from test where [name] = t.[name])

which hasn't any problem

My question is: May i do this better?? I will fetch around 10,000 records from table incremental table(every day 10,000 more results).

Regards

Answer 1

This will select distinct names and the newest date associated with each:

select t.[name], MAX(t.[date]) 
from test t
group by t.[name]

When using GROUP BY, you can also use aggregate functions in the SELECT clause for columns that are not part of the grouping.

From MSDN (Aggregate Functions):

Aggregate functions are frequently used with the GROUP BY clause of the SELECT statement.

Answer 2

What version of SQL Server?

SQL 2005 and up:

SELECT *
FROM
   (SELECT Item = Row_Number() OVER (PARTITION BY [name] ORDER BY [date] DESC), * FROM test) X
WHERE Item = 1

SQL 2000:

SELECT T.*
FROM
   test T
   INNER JOIN (
      SELECT [name], MaxDt = Max([date]) FROM test GROUP BY [name]
   ) X ON T.[name] = X.[name] AND T.[date] = X.MaxDt

If you can have duplicate dates then another step is needed for the sql 2000 version to get it down to one row.

@Oded pointed out that you can simply get the max date. If all you need are the name and date then his query is best. But if my suspicion is correct that you need more items from the same row, then you'll need queries like these.

Here's another SQL 2005 version:

SELECT
   T.*
FROM
   test T
   CROSS APPLY (
      SELECT TOP 1 [date]
      FROM test T2
      WHERE T.[name] = T2.[name]
      ORDER BY T2.[date] DESC
   ) X
WHERE
   T.[date] = X.[date]

This query will have problems with duplicate max dates for the same name

Update

Now that I know it's SQL 2008:

The row_number() solution is simplest and easiest. I'd start with that. If performance isn't enough, and the table is a child of a parent table that has each [name] only once, try the CROSS APPLY solution with the outer table (test T) as the parent:

SELECT
   X.*
FROM
   Parent P
   CROSS APPLY (
      SELECT TOP 1 *
      FROM test T
      WHERE P.[name] = T.[name]
      ORDER BY T.[date] DESC
   ) X

If there is no parent table, you can try the above queries or use SELECT DISTINCT [name] FROM test but I'm not convinced that will be a performance improvement:

SELECT
   X.*
FROM
   (SELECT DISTINCT [name] FROM test) P
   CROSS APPLY (
      SELECT TOP 1 *
      FROM test T
      WHERE P.[name] = T.[name]
      ORDER BY T.[date] DESC
   ) X