0

I am confused by the usage of alias. For example, the query below works fine

select * from 
(
select ROW_NUMBER() over (partition by prodid order by quantity desc) as 'rankin',prodid,quantity from sales
) A
where rankin=1

But when I modify it as shown in the snippet below I get the error: "Invalid column name 'rownumber'". 

select ROW_NUMBER() over (order by quantity) as 'rownumber' from sales
where rownumber = 1

Please explain the difference.

Asad Saeeduddin
  • 46,193
  • 6
  • 90
  • 139
paddy
  • 47
  • 7
  • Erm, in which version of `mysql` did `ROW_NUMBER() OVER` [become available](http://stackoverflow.com/questions/1895110/row-number-in-mysql)? – Wrikken Aug 12 '13 at 23:52

1 Answers1

0

This is because the SELECT is executed after the WHERE and the FROM is executed before the WHERE so it cannot be used in the second query but it can be used in that first query.

ApplePie
  • 8,814
  • 5
  • 39
  • 60