英文:
How to delete duplicate rows in a column in SQL
问题
我运行了一段SQL代码如下所示：
SELECT DISTINCT Track, Artist, Valence, Stream
FROM dbo.[spotify _db]
ORDER BY Stream DESC, Valence DESC
结果如下：
期望的输出应该是
| - | - | - | - |
|---|---|---|---|
| Blinding Lights | The Weeknd | 0.334 | 3386520288</td> |
| Shape of You | Ed Sheeran | 0.931 | 336200520</td> |
| Someone You Loved | Lewis Capaldi | 0.446 | 2634013335</td> |
| rockstar (feat.... | Post Malone | 0.129 | 2594926619</td> |
| Sunflower .... | Post Malone | 0.925 | 2538329799</td> |
| One Dance | Drake | 0.37 | 2522431995</td> |
| Closer | The Chainsmokers | 0.661 | 2456205158</td> |
请注意，Sunflower和Closer应该是一个曲目名称
我尝试使用DISTINCT，以避免显示重复的歌曲标题（因为它们是同一位艺术家），但它仍然会显示。有什么建议吗？先感谢。
英文:
I ran an SQL code that goes:
SELECT DISTINCT Track, Artist, Valence, Stream
FROM dbo.[spotify _db]
ORDER BY Stream DESC, Valence DESC
This resulted to:
The desired output should be
| - | - | - | - |
|---|---|---|---|
| Blinding Lights | The Weeknd | 0.334 | 3386520288</td> |
| Shape of You | Ed Sheeran | 0.931 | 336200520</td> |
| Someone You Loved | Lewis Capaldi | 0.446 | 2634013335</td> |
| rockstar (feat.... | Post Malone | 0.129 | 2594926619</td> |
| Sunflower .... | Post Malone | 0.925 | 2538329799</td> |
| One Dance | Drake | 0.37 | 2522431995</td> |
| Closer | The Chainsmokers | 0.661 | 2456205158</td> |
Notice that Sunflower and Closer should be one track name
I tried using DISTINCT so that the duplicate song titles (because they are of the same artist) will not show but it keeps on showing. Any suggestions? Thanks in Advance
答案1
得分: 1
以下是您要翻译的内容：
"我注意到了一些事情，这是代码。您只能根据排序类型更改优先级
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY TRACK ORDER BY Stream DESC, Valence DESC) AS RW
FROM SONG
) AS A
WHERE A.RW = 1
ORDER BY Stream DESC
英文:
I noticed something, this is the code. You can only change the priority based on the type of sorting
SELECT *
FROM (
SELECT *,ROW_NUMBER() OVER(PARTITION BY tRACK ORDER BY Stream DESC, Valence DESC) RW
FROM SONG
)A
WHERE A.RW=1
ORDER BY Stream DESC
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论