CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Scott Chamberlain
Scott Chamberlain

Reputation: 127583

Finding a better way to do a top 1 per group

I am attempting to find the most recent value per id that is older than 1/1/2013

create table #foo
(
    id int,
    value money,
    entry_date datetime
)

insert into #foo values (1, 1.00, '1/1/2012')
insert into #foo values (1, 2.00, '2/1/2012')
insert into #foo values (1, 7.00, '1/1/2013')
insert into #foo values (2, 1.00, '1/1/2013')
insert into #foo values (2, 1.00, '2/1/2013')
insert into #foo values (3, 5.00, '3/1/2012')

The following gives me the solution but I know I am doing this the wrong way. 

select id, value
from
(
    select id, value, row_number() over (partition by id order by entry_date desc) as ind
    from #foo
    where entry_date < '1/1/2013'
) a where ind = 1

--Results:
--id          value
------------- ---------------------
--1           2.00
--3           5.00

Id 2 is not returned due to not having any records older than 1/1/2013.

What is the correct way to accomplish what I am attempting to do?

Upvotes: 3

Views: 192

Answers (4)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Use option with EXISTS operator

SELECT t.id, t.value
FROM #foo t
WHERE t.entry_date < '1/1/2013'
 AND EXISTS(
            SELECT 1
            FROM #foo t2
            WHERE t.id = t2.id
              AND t2.entry_date < '1/1/2013'
            HAVING MAX(t2.entry_date) = t.entry_date
            )

Demo on SQLFiddle

For improving performance use this index:

CREATE INDEX x ON #foo(id, entry_date) INCLUDE(value)

Upvotes: 1

valverij
valverij

Reputation: 4941

This is along the same lines, but you can also use a TOP 1 WITH TIES in combination with the ROW_NUMBER() to eliminate the need for a subquery:

select top 1 with ties id, value
from #foo
where entry_date < '1/1/2013'
order by row_number() over (partition by id order by entry_date desc)

It's a little cleaner, in my opinion. Unfortunately, it can also perform slightly slower. Still, it's always good to know different uses for SQL functions.

Upvotes: 2

Taryn
Taryn

Reputation: 247760

You could also use a subquery to get the result:

select f1.id, f1.value
from #foo f1
inner join
(
  select id, max(entry_date) entry_date
  from #foo
  where entry_date < '1/1/2013'
  group by id
) f2
  on f1.id = f2.id
  and f1.entry_date = f2.entry_date;

See SQL Fiddle with Demo

Upvotes: 6

Tim Schmelter
Tim Schmelter

Reputation: 460168

With SQL-Server 2005 you have ranking functions and common-table-expressions(CTE).

WITH CTE AS
(
   SELECT id,value,entry_date,
          RN = ROW_NUMBER() OVER (PARTITION BY id ORDER BY entry_date DESC)
   FROM dbo.TableName
   WHERE entry_date < '1/1/2013'
)
SELECT id,value,entry_date FROM CTE WHERE RN = 1

returns the most recent record per id, so ORDER BY entry_date DESC instead of value.

If you want all "max-recent" values in case there are multiple, replace ROW_NUMBER with DENSE_RANK.

Upvotes: 1

Related Questions