Best way to get the latest version of a specific list of records in SQL

Question

I want to query a giant table (SQL Server) for the latest version of a set of record e.g.

Table:

Id, key, Ver
1, A， 1
2, B， 1
3, A， 2
4, B， 2
5，B， 3

Result:

Id, Key, Ver
3, A, 2
5, B, 3

The join method mentioned in here will work but only work well if want to get the latest version of all keys but the join will become a too much overhead if we only care about a subset of keys. so I would like ask whether we should do a loop of

select top 1 * from table where key = 'A' order by ver desc

or

select max(ver), key from table where key in ('A', 'B') group by key

or there is better way doing so? cheers

Ok, but consider either adding appropriate index to make `order by` effective (this might make write operations more costly) or add separate table with latest version value (then you'll need to adjust your code accordingly to provide ACID properties in respect to version value). — Victor Sorokin, Feb 02 '15 at 15:57

Jodrell · Accepted Answer · 2015-02-02T16:39:05.267

Essentially, looping over a set of keys one at a time and performing a operation n times is a bad approach with any RDBMS. It prevents the query engine from optimizing and guarantees n seeks\scans of a table\index.

If you can express your query as a set based operation this will allow the query engine to fully optimize your operation, creating a much more optimal query plan.

If you are using SQL Server 2008 or greater, use RANK,

SELECT
            [Id],
            [Key],
            [Ver]
    FROM
            [Table]
    WHERE
            RANK() OVER (PARTITION BY [Key], ORDER BY [Ver] DESC) = 1;

with more generic SQL,

SELECT
            [T1].[Id],
            [T2].[Key],
            [T2].[Ver]
    FROM (
            SELECT
                        [Key]
                        MAX([Ver]) [Ver]
                FROM
                        [Table]
                WHERE
                        [Key] IN ('A', 'B')
                GROUP BY
                        [Key]) [T2]
        JOIN
            [Table] [T1]
                ON [T1].[Key] = [T2].[Key] AND [T1].[Ver] = [T2].[Ver];

To ensure performance of both queries, create a covering index on Key then Ver.

CREATE UNIQUE NONCLUSTERED INDEX [IX_Table_Key_Ver]
    ON [Table] ([Key], [Ver] DESC);

will ID in (1,2,3,4,5,) be much less efficient (with index) than a loop of ID = 1 ID = 2 ..... ? — John, Feb 02 '15 at 16:03
@John it will certainly be quicker to perform this operation once rather than n times. RDBMS Databases are good at set based operations. — Jodrell, Feb 02 '15 at 16:09

score 0 · Answer 2 · answered Feb 02 '15 at 15:58

0

Use a sub-select to find the max ver for a key:

select * from table t1
where ver = (select max(ver) from table
             where key = t1.key)

answered Feb 02 '15 at 15:58

jarlh

42,561
8
45
63

SoulTrain · Answer 3 · 2015-02-02T16:21:25.763

0

IMO, your second solution should perform as good if not better than any of the other solutions mentioned here.

select max(ver), key from table where key in ('A', 'B') group by key

Also, create an index on the key column.

EDIT:

For the Max version row ID value, you could use a CTE

WITH CTE AS
(
    select max(ver) VER, key from table where key in ('A', 'B') group by key
)
SELECT * FROM TABLE 
INNER JOIN CTE 
ON CTE.KEY=TABLE.KEY
AND CTE.VER=TABLE.VER

edited Feb 02 '15 at 16:21

answered Feb 02 '15 at 16:13

SoulTrain

1,904
1
12
11

how will you get the `Id` of the row with the latest version ? – Jodrell Feb 02 '15 at 16:14

score 0 · Answer 4 · answered Feb 02 '15 at 16:14

0

Here's another approach using an analytic function such as row_number:

select * 
from (
  select *, row_number() over (partition by key order by id desc) rn
  from yourtable
  ) t
where rn = 1

SQL Fiddle Demo

answered Feb 02 '15 at 16:14

sgeddes

62,311
6
61
83

score 0 · Answer 5 · answered Feb 02 '15 at 16:23

In general don't use a TOP 1 statment, ROW_NUMBER() is better. Cursors also aren't a great idea for this (ignoring the technicality on my order by!).

E.g.

SELECT
    A.*
FROM
    (
    SELECT
        ID
        ,Ver
        ,ROW_NUMBER() OVER(PARTITION BY Key ORDER BY Ver DESC) AS Seq
    FROM
        Table
    ) AS A
WHERE
    A.Seq = 1

Should do fine what I believe you're asking for.

Best way to get the latest version of a specific list of records in SQL

5 Answers5