CODEWITHSUNDEEP
sqlsql-server-ce
Meowbits
Meowbits

Reputation: 586

Need to modify this query

I have a query that I thought was working as intended up until a short while ago.

The follow query goes through my 'player' table and updates the 'starter' value to true for every player that has the highest skill at their weight in that school.

For example, if there are 500 players at weight X, it will select all of those players that belong to the same school, and of those it will update the 'starter' value to true for the one that has the highest skill.

UPDATE player 
    SET starter = 'TRUE' 
    WHERE NOT EXISTS
    (
      SELECT school, weight, skill 
      FROM player b " 
      WHERE b.school = player.school 
      AND b.weight = player.weight 
      AND b.skill > player.skill
    )
    AND player.game_id = 1

The problem I ran into now is that if there are 2 players from the same school in the same weight, that also have the same skill, then both have their 'starter' value set to true.

So my question is, what do I need to add to this query to allow only one starter at a weight for a particular school?

I also have a column named 'age' and if I can, I would like to select the lower age of the two where the same skill occurs.

Upvotes: 1

Views: 83

Answers (2)

Peter Lang
Peter Lang

Reputation: 55534

You will have to change your query, so that only those rows are updated, which have no other row for this school/weight with greater skill or lower age.

I'm additionally adding a unique column id in case you have rows with same skill and same age.

Replace

AND b.skill > player.skill

by

AND (    ( b.skill > player.skill )
      OR ( b.skill = player.skill AND b.age < player.age )
      OR ( b.skill = player.skill AND b.age = player.age AND b.id > player.id )
    )

Upvotes: 3

u07ch
u07ch

Reputation: 13702

I don't know how skills are calculated for this have written a replacement Select you could join to it and update the rows accordingly. Have also created some sample data with 9 rows; the last two H and I have same skill and school but different age (by one day.) Assuming you have date of birth a max / min will work. You could still have players with same age so will need to have a mechanism to deal with that. Not sure on the criteria so its not catered for Here.

declare @players table (school varchar(100), weight float, skill int, dob datetime, name varchar(100))

insert into @players
select 'a', 100, 10, GETDATE(), 'a'
union all select 'a', 101, 11, GETDATE(), 'b'
union all select 'a', 102, 12, GETDATE(), 'c'
union all select 'a', 103, 13, GETDATE(), 'd'
union all select 'a', 104, 14, GETDATE(), 'e'
union all select 'a', 105, 15, GETDATE(), 'f'
union all select 'a', 106, 16, GETDATE(), 'g'
union all select 'a', 107, 17, GETDATE(), 'h'
union all select 'a', 107, 17, dateadd(dd,-1,GETDATE()), 'i'

select * from @players
SELECT 
        school, 
        weight, 
        max(skill) as Skill,
        MIN(dot) as DOB
FROM @players b 
group by school, weight

This returns; 8 rows and selects the younger of the 107 weight category players.

school  weight  (No column name)    (No column name)
a   100 10  2012-04-23 08:53:19.877
a   101 11  2012-04-23 08:53:19.877
a   102 12  2012-04-23 08:53:19.877
a   103 13  2012-04-23 08:53:19.877
a   104 14  2012-04-23 08:53:19.877
a   105 15  2012-04-23 08:53:19.877
a   106 16  2012-04-23 08:53:19.877
a   107 17  2012-04-22 08:53:19.877

Upvotes: 0

Related Questions