0

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:

enter image description here

The desired output should be

- - - -
Blinding Lights The Weeknd 0.334 3386520288
Shape of You Ed Sheeran 0.931 336200520
Someone You Loved Lewis Capaldi 0.446 2634013335
rockstar (feat.... Post Malone 0.129 2594926619
Sunflower .... Post Malone 0.925 2538329799
One Dance Drake 0.37 2522431995
Closer The Chainsmokers 0.661 2456205158

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

Charlieface
  • 52,284
  • 6
  • 19
  • 43
alentot1222
  • 11
  • 1
  • 1
    Who wins? Swae Lee or Post Malone? – John Cappelletti May 06 '23 at 22:40
  • 4
    Always best to post sample data and desired results as text – John Cappelletti May 06 '23 at 22:41
  • 2
    Please do not paste images of code or data please paste text – Charlieface May 06 '23 at 23:12
  • I attached the screenshot of how SQL showed the result and how I wanted the result to be. I can't seem to code the table based on Github instructions. – alentot1222 May 06 '23 at 23:14
  • 1
    https://meta.stackexchange.com/questions/356997/new-feature-table-support Also please show your input data, and please explain the logic desired. Why are some artists shown and not others? – Charlieface May 06 '23 at 23:14
  • 2
    Does this help you? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Charlieface May 06 '23 at 23:16
  • I tried the one with TOP 1 WITH TIES solution and it removed the duplicate track names but it did not arrange it according to the track with the highest stream. Is there something else that I need to add? – alentot1222 May 06 '23 at 23:49

1 Answers1

1

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
abolfazl sadeghi
  • 2,277
  • 2
  • 12
  • 20