CODEWITHSUNDEEP
sqlsql-serverperformance
ericd7
ericd7

Reputation: 471

Most efficient way to select the max value of a column and the corresponding data for that row

As a starting point I have the following query. I know that it works and produces the result I'm looking for but could it be done in a faster/more efficient way since it will potentially be processing large amounts of data to find what I want to return.

(ID in the example below is an auto-increment PK field)

select x, y, z
from table
where id = (select max(id) from table where z = someValue)

Upvotes: 0

Views: 306

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I would suggest:

select top (1) x, y, x
from table
where z = somevalue
order by id desc;

For performance, you want an index on (z, id).

Upvotes: 3

digital.aaron
digital.aaron

Reputation: 5707

I like using a CTE for this, as you can do an INNER JOIN with the source table and the CTE.

;WITH LookupCTE AS 
(
    select id = MAX(id) 
    FROM table 
    WHERE z = someValue
)
SELECT x, y, x
from table t1
INNER join LookupCTE t2 ON t1.id = t2.id

Upvotes: 2

Related Questions