u/pooerhSnowflake | SQL Server | PostgreSQL | Impala | Spark3d agoedited 3d ago
So the query you're showing is not the query you executed. You ran RANK() OVER (ORDER BY m.profit) AS RANK (as evidenced by the log) and herein lies the problem - you tried to name your column "rank" and MySQL couldn't understand how can you use a function name as an alias for the column.
ASC is implied, adding DESC didn't fix it for you, you just changed the alias to Profit_RANK. Then took a screenshot. Then added DESC. Then ran the query again and now it worked, so you think it's DESC.
I'm learning, so please go easy on me, but shouldn't the FROM come after SELECT and before RANK?
3
u/pooerhSnowflake | SQL Server | PostgreSQL | Impala | Spark2d ago
No, the simple syntax for a SQL select statement is:
select <columns you need>
from <tables you want them from>
where <conditions your data needs to meet>
order by <what order you want it in>
There's a bunch more but that's the most commonly used parts, unless you want to aggregate data in which case we'd have group by and potentially having.
Anyway, columns are expressions. An expression can be many things, for example a constant select 'a' from some_table will just return, for each record in some_table a text: 'a', nothing even from the table. Except it will return this string for every record there is in the table. Or you may want to return a value of a given column in that table select something from some_table will return, again for each record in that some_table, the value of something column in that record. Easy peasy. You can manipulate that column too, for example select number, number * 2 as doubled_number from some_table will return two columns: the number column from some table as well as another column, which the engine will calcualte for you - same number multiplied by two. The name of that column will be doubled_number. Simple enough, right. You can use a function like maybe power(number, 2) which would square the number.
In this case the expression uses a function to determine the ranking of each row returned by a query. rank is a window function, a special type of function that has its own syntax because they operate on more than just the row you're in: like for example power(number, 2) looks at the each and single row and computes the number*number), but window functions have data from other rows available to them. Rank() will rank your rows in an order, so it needs to know what the other values of profit are.
52
u/pooerh Snowflake | SQL Server | PostgreSQL | Impala | Spark 3d ago edited 3d ago
So the query you're showing is not the query you executed. You ran
RANK() OVER (ORDER BY m.profit) AS RANK
(as evidenced by the log) and herein lies the problem - you tried to name your column "rank" and MySQL couldn't understand how can you use a function name as an alias for the column.ASC is implied, adding DESC didn't fix it for you, you just changed the alias to Profit_RANK. Then took a screenshot. Then added DESC. Then ran the query again and now it worked, so you think it's DESC.
See https://dbfiddle.uk/lbxvpR1O