1

I want to do this, to get all the rows for a given a,b combo that have the highest value of c:

SELECT a, b, c
FROM x
WHERE RANK() OVER (PARTITION BY a,b ORDER BY c DESC) = 1

It fails, saying invalid column reference 'c': (possible column names are: a, b, c).

I can wrap the RANK() in a subselect, and check the value outside:

SELECT a, b, c
FROM (
  SELECT a, b, c, RANK() OVER (PARTITION BY a,b ORDER BY c DESC) AS r
  FROM x
) rq
WHERE r = 1

Any idea why I can't do this in the WHERE clause of the simple query? Is it because of it being an aggregate/window function, so has to be done after the WHERE, like a GROUP BY?

PhilHibbs
  • 539
  • 1
  • 6
  • 20
  • SELECT a, b, MAX(c) FROM x GROUP BY a,b ? – Akina Dec 06 '18 at 12:35
  • 1
    why I can't do this in the WHERE clause of the simple query? The order of WHERE sub-conditions execution is not defined. So, when window function executing before and after another conditions applying, the frames (source data sub-arrays) will differ. So window function cannot posess in WHERE clause. – Akina Dec 06 '18 at 12:44
  • @Akina A simple MAX() would not return multiple records. – PhilHibbs Dec 07 '18 at 13:30
  • Do you need a pack of full duplicates really? It's more simple to add COUNT(*) into output list and generate proper record copies on the client side... – Akina Dec 07 '18 at 18:58
  • Yes I do need the duplicates. – PhilHibbs Dec 11 '18 at 11:56
  • If so use subselect-wrapping query (or use CTE). – Akina Dec 11 '18 at 12:08

0 Answers0