How can I limit my query to only return the first result found for each ID?-CodePudding

I'm trying to select only the first result for each PlayerID.

So I tried this query using DISTINCT :

SELECT DISTINCT CharacterName, PlayerID, Class, Race, Alignment, Weapon
FROM CharacterTable

But it is still giving me all rows for each character like this:

CharacterName      PlayerID     Class     Race     Alignment     Weapon
----------------------------------------------------------------------------
Racx               55           Fighter   Human    Chaotic Good  Sword   
Racx               55           Fighter   Human    Chaotic Good  Spear  
Racx               55           Fighter   Human    Chaotic Good  Dagger
Elix               41           Cleric    Orc      Lawful Good   Mace
Elix               41           Cleric    Orc      Lawful Good   Club
Marra              97           Wizard    Elf      Neutral       Staff
Lyrss              76           Thief     Human    Chaotic Good  Dagger
Lyrss              76           Thief     Human    Chaotic Good  Knife
Lyrss              76           Thief     Human    Chaotic Good  Dart
Lyrss              76           Thief     Human    Chaotic Good  Bow

I only want the first result found for each character.

Is this possible?

Thanks!

CodePudding user response：

Does this help?

SELECT * FROM 
(
    SELECT *
      ,RANK() OVER   
        (PARTITION BY PLAYERID ORDER BY Weapon DESC) AS Rank   FROM TEST003
        ) AA 
WHERE Rank =1