Calculate rank so that the smaller number is higher rank
Options

JR_S1L
Member
I need to calculate the rank on a metric where the lower value is higher in rank, and the higher values are lower in rank. This is how I've been calculating standard rank (where higher values are high in rank)
SELECT `Metric`
,`TimeFrame`
,`VALUE`
,`Name`
,`Grouping`
,`ProcessorName`
,CASE
WHEN @priorMetric = `MetricID` AND `VALUE` = @priorValue THEN @rank := @rank
WHEN @priorMetric = `MetricID` THEN @rank := @rank + 1
else @rank := 1
END AS Rank
,@priorMetric := `MetricID` as MetricID
,@priorValue := `VALUE`
FROM sort
JOIN(SELECT @rank := 0, @priorMetric:= '', @priorValue := 0) r
Tagged:
0
Comments
-
How is your
sort
table defined? Can you just make sure you're sorting the values in ASC instead of DESC?**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Categories
- All Categories
- Product Ideas
- 2.1K Ideas Exchange
- Connect
- 1.3K Connectors
- 309 Workbench
- 7 Cloud Amplifier
- 10 Federated
- Transform
- 666 Datasets
- 120 SQL DataFlows
- 2.3K Magic ETL
- 827 Beast Mode
- Visualize
- 2.6K Charting
- 90 App Studio
- 46 Variables
- Automate
- 198 Apps
- 489 APIs & Domo Developer
- 98 Workflows
- 24 Code Engine
- AI and Machine Learning
- 23 AI Chat
- 4 AI Projects and Models
- 18 Jupyter Workspaces
- Distribute
- 118 Domo Everywhere
- 284 Scheduled Reports
- 11 Software Integrations
- Manage
- 145 Governance & Security
- 13 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 117 Community Announcements
- 5K Archive