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.
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!
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
I would select your subqueries into a temporary tables before selecting from them. I think you'd see a significant performance boost from that.
