SQL Server Arrange cities based on their ranking [closed]

wixjitnu 于 2023-02-18 发布在其他

关注(0)|答案(1)|浏览(101)

Closed. This question needs details or clarity . It is not currently accepting answers.

Want to improve this question? Add details and clarify the problem by editing this post .

I need to display ranking along with city based on city selection. Suppose if someone select Nagpur, we need to show the predecessor city, selected city and followed by successor city with their respective ranking.

Criteria

If someone selects Mumbai then, No Data, Mumbai-1, Delhi-2
If someone selects Delhi then, Mumbai-1, Delhi-2, Lucknow-3
If someone selects Nagpur then, Lucknow-3, Nagpur-3, Pune-3 (based on city's sorting order alphabetically, if there are multiple cities having same ranking)
If someone selects Kanpur then, Patna-7, kanpur-8, No Data.

The data is in this format:

Can someone help to achieve this? I am using SQL Server 2016

sql-server

来源：https://stackoverflow.com/questions/75467645/arrange-cities-based-on-their-ranking

1条答案

按热度按时间

bxgwgixi1#

It may not be the best solution, but it will be useful for now. I recommend that you always add an id to the tables and the names of the fields can be easily identified to which table they belong

DROP TABLE #TableTempRankingCity
 DECLARE @Id INT;

 SELECT ROW_NUMBER() OVER(
 ORDER BY [City]) AS fila, *
 INTO #TableTempRankingCity
 FROM RankingCiudades
 ORDER BY Rank,City

 --SELECT * FROM #TableTempRankingCity
 SELECT @Id = fila
 FROM #TableTempRankingCity
 WHERE City = 'Lucknow'

 SELECT 
    City 
    ,Rank
 FROM #TableTempRankingCity 
 WHERE fila IN (@Id-1,@Id+1,@Id+2)

赞(0）回复(0）举报 2023-02-18

我来回答

SQL Server Arrange cities based on their ranking [closed]

1条答案

相关问题

热门标签

最新问答