sql - Sub Sorting in Oracle -
i have demographics database have list of population numbers per continent, country, state , city. have added rank column table need update. ranking needs happen @ state level. there rank 1..n each city within state. ranking again begin 1..n state.
(note: city names can duplicate.. i.e. city might exist in more 1 state there 2 different rows if happens. pk continent,cntry,state).
i've been trying sorts of select subqueries , rank function variations cant head round it. can help?
to rank per state need partition by:
rank() on (partition continent, cntry, state order poulation desc)
if there's city twice state might switch row_number instead.
and because small table might consider calculating rank dynamically within view instead of potentially updating large number of rows whenever there's update/insert.
Comments
Post a Comment