CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
YoungboyVBA
YoungboyVBA

Reputation: 317

SNOWFLAKE QUESTION: Using the RANK () Windows Function in SQL- where the column you want to order by isn't ordinal

So, here's a sample of my data:

Sample Data

Essentially, I want to rank the vehicles latitude coordinates (to see where the most common places to park are). However, when I try and use the rank() windows function:

RANK () OVER ( PARTITION BY Vehicle_ID ORDER BY Latitude DESC ) RANKINGS

However, this assumes the latitude column is ordinal and thus, ranks them according to that (placing the highest latitude as #1).

I'm guessing I need to add a frequency column first (which I've not been able to to). Once I've got that, would I need to partition by both the latitude AND vehicle ID columns.

The desired output would be to have rankings alongside latitude, group by vehicle ID

Upvotes: 2

Views: 8080

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175606

Additional count could be addedd:

WITH cte AS (
  SELECT *, COUNT(*) OVER(PARTITION BY Vehicle_Id, Latitude) AS cnt
  FROM tab
)
SELECT *, RANK() OVER(PARTITION BY Vehicle_ID ORDER BY cnt DESC) AS RANKINGS
FROM cte;

Upvotes: 4

Related Questions