Reputation: 169
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
Upvotes: 0
Views: 1541
Reputation: 50282
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
Upvotes: 2
Reputation: 499392
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.
Upvotes: 2