The fastest way to get the most recent records [duplicate]

Question

I am looking for the fastest way to return the most recent record for each reference number.

I quite liked the solution from BrentOzar.com, but it does not seem to work when I add a third condition (SequenceId). It appears to work only when I specify the Id and the creation date.

To understand my problem, You will need to create the modified sample table, which is essentially a copy of the table on the above referenced website but with a little twist.

CREATE TABLE [dbo].[TestTable](
      [Id] [int] NOT NULL,
      [EffectiveDate] [date] NOT NULL,
      [SequenceId] [bigint] IDENTITY(1,1) NOT NULL,
      [CustomerId] [varchar](50) NOT NULL,
      [AccountNo] [varchar](50) NOT NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
      [Id] ASC,
      [EffectiveDate] ASC,
      [SequenceId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[TestTable] ON
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo]) 
VALUES (1, CAST(0xDF300B00 AS Date), 1, N'Blue', N'Green')
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo]) 
VALUES (2, CAST(0xDF300B00 AS Date), 2, N'Yellow', N'Blue')
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo]) 
VALUES (1, CAST(0xE0300B00 AS Date), 3, N'Red', N'Yellow')
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo]) 
VALUES (3, CAST(0xE0300B00 AS Date), 4, N'Green', N'Purple')
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo]) 
VALUES (1, CAST(0xE1300B00 AS Date), 5, N'Orange', N'Purple')
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo]) 
VALUES (2, CAST(0xE3300B00 AS Date), 6, N'Blue', N'Orange')
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo]) 
VALUES (3, CAST(0xE6300B00 AS Date), 7, N'Red', N'Blue')
SET IDENTITY_INSERT [dbo].[TestTable] OFF
GO

If I run a similar query as on the website, I get exactly the same result.

SELECT tt.*
FROM dbo.TestTable tt
LEFT OUTER JOIN dbo.TestTable ttNewer
ON tt.id = ttNewer.id AND tt.EffectiveDate < ttNewer.EffectiveDate
WHERE ttNewer.id IS NULL

However, the little twist is that I added a SequenceId column to the table as you may have noticed. The purpose of this column is because the client may want to do a post dated entry for a date in the past. This entry must supercede the other entries made on the same date which is in the past. If I run the query before I add the the post dated entries, I get the same result as previously.

SELECT tt.*
FROM dbo.TestTable tt
LEFT OUTER JOIN dbo.TestTable ttNewer
ON    ( 
            tt.id = ttNewer.id AND tt.EffectiveDate < ttNewer.EffectiveDate
            AND tt.SequenceId < ttNewer.SequenceId
      )
WHERE ttNewer.Id IS NULL

If I add two post dated entries as shown below, then I start to get interesting results.

INSERT INTO TestTable(Id,EffectiveDate,CustomerId,AccountNo) values
(
      2,'20090103','Blue','Orange'
);

INSERT INTO TestTable(Id,EffectiveDate,CustomerId,AccountNo) values
(
      2,'20090105','Blue','Orange'
);

What you should notice is that the two queries below no longer return the last record whether I use the one which is similar to what is on your website or I use the one which adds another condition (SequenceId)

SELECT tt.*
FROM dbo.TestTable tt
LEFT OUTER JOIN dbo.TestTable ttNewer
ON tt.id = ttNewer.id AND tt.EffectiveDate < ttNewer.EffectiveDate
WHERE ttNewer.id IS NULL

SELECT tt.*
FROM dbo.TestTable tt
LEFT OUTER JOIN dbo.TestTable ttNewer
ON    ( 
            tt.id = ttNewer.id AND tt.EffectiveDate < ttNewer.EffectiveDate
            AND tt.SequenceId < ttNewer.SequenceId
      )
WHERE ttNewer.Id IS NULL

What I would like the query to do is to return the last record for a reference number (Id) based on the last sequence number for any given day. In other words, the record with the last sequence number on the most recent EffectiveDate.

If you have that sequence that goes up regardless of the other data, why isn't that the only piece of data in the primary key? Seems like you're just making life harder than it needs to be. — corsiKa, Commented Feb 10, 2016 at 6:02

Aaron Bertrand · Accepted Answer · 2016-02-09 23:55:46Z

Self-joins seem cheap at low row counts, but I/O is exponential as the row count increases. I would prefer to solve this the CTE way, unless you are on SQL Server 2000 (please always specify the version you need to support, using a version-specific tag):

;WITH cte AS 
(
  SELECT Id, EffectiveDate, SequenceId, CustomerId, AccountNo, 
    rn = ROW_NUMBER() OVER (PARTITION BY Id 
      ORDER BY EffectiveDate DESC, SequenceId DESC)
  FROM dbo.TestTable
)
SELECT Id, EffectiveDate, SequenceId, CustomerId, AccountNo
  FROM cte
  WHERE rn = 1
  ORDER BY Id; -- because you can't rely on sorting without ORDER BY

This still has to scan, but it only has to scan once, compared to all the self-join variants, which will always have two scans (or potentially a scan and a seek executed multiple times, with better indexes).

If you want a more efficient query (eliminating an expensive sort, at the potential cost of writes, and perhaps other queries that don't need to support this sorting), change the primary key to match the query pattern:

PRIMARY KEY CLUSTERED 
(
  [Id] ASC,
  [EffectiveDate] DESC,
  [SequenceId] DESC
)

The direction of the secondary columns has no effect on uniqueness, and should change writes minimally as long as the table isn't terribly wide and there aren't an extremely large number of rows per id.

Community · Accepted Answer · 2020-06-15 09:05:18Z

The greatest-n-per-group tag has a number of questions and answers relevant to this type of problem, with the canonical example for SQL Server being:

Retrieving n rows per group

With the two main options being:

ROW_NUMBER (as in Aaron's answer); and
APPLY

So while the question is most likely a duplicate of that (from the point of view that the answer techniques are the same), here is a specific implementation of the APPLY solution pattern for your particular problem:

SELECT 
    CA.Id, 
    CA.EffectiveDate, 
    CA.SequenceId, 
    CA.CustomerId, 
    CA.AccountNo
FROM 
(
    -- Per Id
    SELECT DISTINCT Id 
    FROM dbo.TestTable
) AS TT
CROSS APPLY
(
    -- Single row with the highest EffectiveDate then SequenceId
    SELECT TOP (1) TT2.*
    FROM  dbo.TestTable AS TT2
    WHERE TT2.Id = TT.Id
    ORDER BY TT2.EffectiveDate DESC, TT2.SequenceId DESC
) AS CA
ORDER BY 
    CA.Id;

The logic is rather simple:

Obtain the set of unique IDs
Find the single row we want for each ID

The existing indexing makes the execution plan equally simple:

Results from this plan shape will stream to the client as soon they become available (rather than all at once at the end of server-side processing). The Stream Aggregate is the only partially-blocking operator in the plan: it receives rows in ID order, so as soon as the second ID is encountered, the aggregate can return its first result to the nested loops join, and so on.

The clustered index is useful both for providing rows in order to the Stream Aggregate, and for a very efficient single-row lookup per ID (in descending order). This avoids any unnecessary blocking sorts in the plan. It should be quite an efficient solution, unless there are very many IDs, few rows per ID on average, and suitable indexing is provided for an alternate approach.

The ROW_NUMBER solution could be just as efficient - perhaps more so, depending on the data distribution - but the SQL Server query processor cannot currently make use of the provided index to avoid a sort (though it logically could).

Test Results

On the larger data set helpfully provided in Mister Magoo's answer, the execution plan stays basically the same, but uses parallelism:

Test results on my machine for the three methods are:

Of course this is a little unfair to the ROW_NUMBER method, because the provided indexing is not optimal for that solution.

Mister Magoo · Accepted Answer · 2016-02-10 01:02:37Z

An alternative you might consider is nested groups, such as this.

select tt.Id, tt.EffectiveDate, tt.SequenceId, tt.CustomerId, tt.AccountNo
from dbo.TestTable tt
join (
  -- Find maximum SequenceID for each maximum EffectiveDate for each Id
  select it.id, it.EffDate, max(t1.SequenceId) SeqId
  from dbo.TestTable t1
  join (
    -- Find maximum EffectiveDate for each Id
    select t0.id, max(t0.EffectiveDate) EffDate
    from dbo.TestTable t0
    group by t0.id
    ) it
  on t1.id = it.id
  and t1.EffectiveDate = it.EffDate
  group by it.id, it.EffDate
  ) tg
on tg.id = tt.id
and tg.EffDate = tt.EffectiveDate
and tg.SeqId = tt.SequenceId
order by tt.id;

It has quite a different plan to the CTE/row_number method, but could be more efficient in some cases.

In the test harness below, the nested groups method comes out at an average duration of roughly 300 ms for 1M rows input, while the CTE/ROW_NUMBER comes out at about 600 ms for the same input data.

Now, this is one test (run 10 times) against fake data, so your actual results will be different, but please do test both methods to see which suits your purpose best.

Of course, if, as I suspect, you are targeting one particular ID each time, rather than the whole table, then I would suggest Aaron's CTE is better simply for ease of reading/maintenance, and they will likely both perform sufficiently fast to make that choice simple.

Test Harness:

USE tempdb;
GO

--== CREATE SOME TEST DATA IF WE DON'T ALREADY HAVE IT

IF OBJECT_ID('[dbo].[TestTable]') IS NULL
BEGIN
  CREATE TABLE [dbo].[TestTable](
        [Id] [int] NOT NULL,
        [EffectiveDate] [date] NOT NULL,
        [SequenceId] [bigint] IDENTITY(1,1) NOT NULL,
        [CustomerId] [varchar](50) NOT NULL,
        [AccountNo] [varchar](50) NOT NULL,
  CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
  (
        [Id] ASC,
        [EffectiveDate] ASC,
        [SequenceId] ASC
  ) 
  );

  INSERT dbo.TestTable(Id, EffectiveDate, CustomerID, AccountNo)
  SELECT TOP 1000000 abs(checksum(newid()))%1000, dateadd(day, abs(checksum(newid()))%1000, '1 jan 2009'), datename(dw, dateadd(day, abs(checksum(newid()))%1000, '1 jan 2009')), datename(month,dateadd(day, abs(checksum(newid()))%1000, '1 jan 2009'))
  FROM sys.all_columns a1, sys.all_columns a2

  --== UNCOMMENT TO CHECK THE SAMPLE DATA IS GOOD
  --SELECT *
  --FROM dbo.TestTable
  --ORDER BY ID, EffectiveDate, SequenceId;
END

--== CREATE SOMEWHERE TO STORE THE TIMINGS

  if object_id('tempdb..#results') is not null drop table #results;

  create table #results(
    name nvarchar(50) not null, 
    startTime datetime2 not null default(sysutcdatetime()), 
    endTime datetime2 null, 
    rows int null, 
    duration as (datediff(millisecond, startTime, endTime))
    );

  create clustered index #ix_results on #results(name, endTime);
go

--== CLEAN UP BEFORE EACH RUN

dbcc freeproccache;
dbcc dropcleanbuffers;

--== AARON'S CTE
go

declare @id int, @Ed date, @sid bigint, @cid varchar(50), @ano varchar(50);

insert #results(name) values('Aaron''s CTE');

;WITH cte AS 
(
  SELECT Id, EffectiveDate, SequenceId, CustomerId, AccountNo, 
    rn = ROW_NUMBER() OVER (PARTITION BY Id 
      ORDER BY EffectiveDate DESC, SequenceId DESC)
  FROM dbo.TestTable
)
SELECT @id = Id, @Ed = EffectiveDate, @sid = SequenceId, @cid = CustomerId, @ano = AccountNo
  FROM cte
  WHERE rn = 1
  ORDER BY Id; -- because you can't rely on sorting without ORDER BY

update #results set endTime = sysutcdatetime(), rows=@@rowcount where name='Aaron''s CTE' and endTime is null;

go 10

dbcc freeproccache;
dbcc dropcleanbuffers;

--== MAGOO'S NESTED GROUPS
go
declare @id int, @Ed date, @sid bigint, @cid varchar(50), @ano varchar(50);

insert #results(name) values('Magoo''s Nested Groups');

SELECT @id = tt.Id, @Ed = tt.EffectiveDate, @sid = tt.SequenceId, @cid = tt.CustomerId, @ano = tt.AccountNo
from dbo.TestTable tt
join (
  -- Find maximum SequenceID for each maximum EffectiveDate for each Id
  select it.id, it.EffDate, max(t1.SequenceId) SeqId
  from dbo.TestTable t1
  join (
    -- Find maximum EffectiveDate for each Id
    select t0.id, max(t0.EffectiveDate) EffDate
    from dbo.TestTable t0
    group by t0.id
    ) it
  on t1.id = it.id
  and t1.EffectiveDate = it.EffDate
  group by it.id, it.EffDate
  ) tg
on tg.id = tt.id
and tg.EffDate = tt.EffectiveDate
and tg.SeqId = tt.SequenceId
order by tt.id;

update #results set endTime = sysutcdatetime(), rows=@@rowcount where name='Magoo''s Nested Groups' and endTime is null;

go 10

--== SUMMARISE THE RESULTS

select 
  name, 
  rows, 
  min(duration) as MinimumDuration,
  max(duration) as MaximumDuration,
  avg(duration) as AverageDuration
from #results
group by name, rows;

Stack Exchange Network

The fastest way to get the most recent records [duplicate]

3 Answers 3

Test Results

Not the answer you're looking for? Browse other questions tagged
sql-server
t-sql
performance
greatest-n-per-group
query-performance
or ask your own question.

Linked

Hot Network Questions

The fastest way to get the most recent records [duplicate]

3 Answers 3

Test Results

Not the answer you're looking for? Browse other questions tagged sql-servert-sqlperformancegreatest-n-per-groupquery-performance or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
t-sql
performance
greatest-n-per-group
query-performance
or ask your own question.