Making a SQL Query More Efficient

Question

I have a query along the lines of

select b.* from
(select key, max(val) as val from (somequery) group by key) as a
inner join
(somequery) as b
on a.key = b.key and a.val = b.val
order by key

And I was wondering if there is an obvious way (that I am missing) to simplify it (given that somequery might be rather long).

Any thoughts would be appreciated.

Answer 1

There is, but it's certainly not obvious:

select
    *
from
    (
    select
        key,
        val,
        col,
        max(val) over (partition by key) as MaxVal
    from
        tableA
    )
where
    val = MaxVal

Using the over clause is a great way to do this, and doesn't require any extraneous subqueries. All it does is take the max val per each key, and then wraps that result set in a subquery, where we can check the val against MaxVal to make sure we're pulling the correct row.

Much cleaner and faster than doing up to three subqueries!

Answer 2

I would select your subqueries into a temporary tables before selecting from them. I think you'd see a significant performance boost from that.

Answer 3

You want to use ROW_NUMBER() or RANK() for this.

(and please make sure that a previous query ends with a semi-colon)

with ranked as
(
select *, row_number() over (partition by key order by val desc) as bestrow
from sometableorquery 
)
select *
from ranked
where bestrow = 1
order by key;

If you want ties (so that a key that has two best values returns both), then use rank() instead of row_number().

Rob