Row_Number недоступен в предложении куда, но в порядке

Question

Я пытаюсь использовать Row_Number, он работает нормально в предложении order by, но при использовании его в предложении where, я получаю недопустимый столбец, что не имеет смысла для меня?

Кто-нибудь может объяснить, почему это так? Огромное спасибо

SELECT col1,col2,
ROW_NUMBER() OVER(PARTITION BY col2 ORDER BY col2) as rownr
FROM  table1 t1 WITH(NOLOCK) 
JOIN table2 t2 WITH(NOLOCK) ON t2.id = t1.id
WHERE rownr > 1
ORDER BY rownr,unit

Answer 1

Логический processing из order by после select но where предложение обрабатывается до select вот почему вы получаете эту ошибку.

Для этого вы должны сделать запрос как sub-select и фильтровать записи во внешнем запросе

SELECT col1,
       col2
FROM  (SELECT col1,
              col2,
              Row_number()OVER(PARTITION BY col2 ORDER BY col2) AS rownr,
              unit
       FROM   table1 t1 WITH(NOLOCK)
              JOIN table2 t2 WITH(NOLOCK)
                ON t2.id = t1.id) a
WHERE  rownr > 1
ORDER  BY rownr, unit 

проверьте здесь для получения дополнительной информации о порядке логической обработки оператора SELECT

Answer 2

ROW_NUMBER() нельзя использовать с тем же запросом. Делать ORDER BY во внешнем запросе.

РЕДАКТИРОВАТЬ: Вы не можете использовать rownr в том же запросе, так как вы вычисляете ROW_NUMBER() в одном месте. Вы можете получить доступrownrво внешнем запросе.

SELECT * FROM
(
   SELECT col1,col2,unit,
   ROW_NUMBER() OVER(PARTITION BY col2 ORDER BY col2) as rownr
   FROM  table1 t1 WITH(NOLOCK) 
   JOIN table2 t2 WITH(NOLOCK) ON t2.id = t1.id       
)TAB
WHERE rownr > 1
ORDER BY rownr,unit