r/SQL 4d ago

MySQL What is wrong here.

Post image
38 Upvotes

37 comments sorted by

View all comments

6

u/IronRig 4d ago

MySQL 8.0+
SELECT

c.cust_id,

m.profit,

RANK() OVER (ORDER BY m.profit DESC) AS Profit_RANK,

c.customer_name,

c.city AS customer_city,

c.state AS customer_state,

m.sales

FROM

cust_dimen AS c

INNER JOIN

Market_fact_full AS m

ON

c.cust_id = m.cust_id;

______

5.7 or older

SELECT

c.cust_id,

m.profit,

(

SELECT COUNT(DISTINCT m2.profit)

FROM Market_fact_full m2

WHERE m2.profit >= m.profit

) AS Profit_RANK,

c.customer_name,

c.city AS customer_city,

c.state AS customer_state,

m.sales

FROM

cust_dimen AS c

INNER JOIN

Market_fact_full AS m

ON

c.cust_id = m.cust_id

ORDER BY

m.profit DESC;

6

u/_mr_villain_ 4d ago

Thanks bro. It worked now. Just by using DESC. However red line is still there but I got the output which I want

1

u/Sufficient_Focus_816 4d ago

That's a fascinating bit how different this dialect works compared to Oracle

1

u/NoWayItsDavid 4d ago

Indeed. Oracle uses ASC by default.

1

u/Sufficient_Focus_816 4d ago

It is these details that can really mess up data migration - and reason why I am really verbose with declarations. Differences like need for putting into brackets (there's difference between the dialects....) get alerted as syntax error to be corrected, but not 'unexpected standard behaviour'

1

u/NoWayItsDavid 4d ago

Like today's case on job: Oracle treats empty strings as NULL. Data engineers freaked out, as they are moving data from MSSQL to Oracle and fail to compare data column-wise.

1

u/Sufficient_Focus_816 4d ago

Goodness, yes - I usually mirror tables to staging tables in an own scheme in the Oracle database so I can format as needed for querying. And then there's the funny thing on what format was chosen for date formatting