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

So, here's a sample of my 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

CodePudding user response：

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;