CODEWITHSUNDEEP
sqlpostgresql
Lostsoul
Lostsoul

Reputation: 25991

row_number error when trying to rank items

I'm trying to get back into SQL queries and am having a frustrating problem.

I'm trying to take all items in my dataset and rank them by partitions. I researched this and think it should look like this:

select g.ticker, g.sector, g.industry, g.countryname, g.exchange, c.carbon, c.year,
    ROW_NUMBER() OVER (
    PARTITION BY g.sector, g.industry, g.countryname, g.exchange
    ORDER BY c.carbon DESC
    ) AS 'Rank'
from "General" g 
INNER JOIN carbon c ON upper(c.ticker) =g.ticker ;

The output would be a rank for each group in the partition in this case it would be sector, industry, country name and exchange then the rows are ranked based on their carbon emissions.

I'm getting this error:

Error occurred during SQL script execution

Reason:
SQL Error [42601]: ERROR: syntax error at or near "'Rank'"
  Position: 1305

if I remove the rank section, the data joins and provides results (obviously not ranked like I want but I know the base query works). What am I doing wrong?

Second (related) question, I forgot how much I hated SQL error messages. The above error tells me there's a syntax error then I went to the docs and couldn't see anything different in my code vs their example. Assuming lack of experience, is there a better way to get actionable error messages (i.e. in Python I get a stack trace that I can read to see what part of my code went wrong)?

Upvotes: 2

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Don't use single quotes for column aliases. Also, I would suggest avoiding anything that is part of standard SQL (which has a rank() function. I often use seqnum:

select g.ticker, g.sector, g.industry, g.countryname, g.exchange, c.carbon, c.year,
       row_number() over (
           partition by g.sector, g.industry, g.countryname, g.exchange
           order by c.carbon desc
                         ) as seqnum
from "General" g join
     carbon c 
     on upper(c.ticker) = g.ticker ;

Note: You should only use single quotes for string and date constants. If you want to escape a column name, use double quotes (just as your query does for the table name General).

Upvotes: 3

Related Questions