Executing SQL query more efficiently

Question

I have a row request

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 (which I am skipping) to simplify it (given that somequery can be quite long).

Any thoughts would be appreciated.

Answer 1

There is, but this, of course, is not obvious:

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

Using over is a great way to do this and does not require any extraneous subqueries. All that is required is to take max val for each key, and then wrap this result set in a subquery, where we can check val on MaxVal to make sure that we pull the correct line.

Much cleaner and faster than up to three subqueries!

Answer 2

For this you want to use ROW_NUMBER () or RANK ().

(and make sure the previous request ends with a semicolon)

 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 need relationships (so a key with two best values ​​returns both), use rank () instead of row_number ().

Rob

Answer 3

I would select your subqueries in temporary tables before selecting them. I think you will see a significant increase in productivity.