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

Question

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!

use [row_number()](https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver16) — Squirrel, Jan 31 '23 at 02:29
@Squirrel Oh so like this? `ROW_NUMBER() OVER(PARTITION BY PlayerID ORDER BY CharacterName) ` ? — SkyeBoniwell, Jan 31 '23 at 02:38
Yes. Do try it out and see if it gives you the expected result — Squirrel, Jan 31 '23 at 02:49
See also [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group). — T N, Jan 31 '23 at 03:08
*first result* -> this implies some kind of **ordering** - you need to be clear on **what** you're going to be ordering by. Some date? Some id ? The weapon assigned to your character? — marc_s, Jan 31 '23 at 05:53

score 1 · Accepted Answer · answered Jan 31 '23 at 05:55

1

Does this help?

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

answered Jan 31 '23 at 05:55

HimanshuSinha

1,650
2
6
10

1

`RANK()` is appropriate if you want to allow multiple results per partition in case of an order-by tie. `ROW_NUMBER()` should be used to never allow more than one. Although the current sample data might not have conditions where this makes a difference, but it is always better to code for the intent ("first result for each PlayerID") in case it does. – T N Feb 01 '23 at 04:03
@TN Should I switch out `RANK() OVER` with `ROW_NUMBER() OVER`? Thanks – SkyeBoniwell Feb 01 '23 at 18:57
1

Yes. It might never make a difference, but if you ever ran into a case where it did, you would want `ROW_NUMBER()` for your stated requirement. – T N Feb 02 '23 at 00:10

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

1 Answers1