1

Sorry, this is my first time using this forum. Apparently people can edit my post which although helpful, has taken some information out. I will try to make it more understandable.

I am using SQL Compact 3.5 as a local database. The program is written in VB.NET.

The problem is with querying one of my tables that is taking too long.

The player table has, among other things, id, skill, school, weight, starter.

  • id is the player's id
  • skill is the player's skill level
  • school is a foreign key pointing to the id of the school table
  • weight is one of 14 different numbers

What I am trying to do is set the starter value = 'true' for the player with the highest skill at a given weight for a given school. So if there are 100 players at a school, there will be 14 starters, one for each weight.

The player table has 170,000 players, each having 1 of 14 different weights, and each belongs to 1 of 4500 schools.

Someone commented below and showed this statement which appears to be on the right track. I am a novice and have not gotten it implemented quite yet.

  "UPDATE p " &
  "SET starter = 'TRUE' " &
  "FROM player p" &
  "JOIN (" &
  "SELECT DISTINCT school, weight, MAX(skill) AS MaxSkill " &
  "FROM player " &
  "GROUP BY school, weight" &
  ") q ON q.school = p.school AND q.weight = p.weight AND q.MaxSkill =
   p.skill"
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Meowbits
  • 586
  • 3
  • 9
  • 28

2 Answers2

3

Instead of doing a group-by-group, row-by-row approach, this update query does it all at once:

First, it gathers the highest skill for each school / weight combination.

It then joins that to the player that has the matching school / weight / skill combination, and then sets that player to the starter.

UPDATE p
SET starter = 'TRUE'
FROM player p 
JOIN (
    SELECT school, weight, MAX(skill) AS MaxSkill
    FROM player
    GROUP BY school, weight
) maxResults 
    ON maxResults.school = p.school 
    AND maxResults.weight = p.weight 
    AND maxResults.MaxSkill = p.skill

However, in the case of a tie in skill, all players with the highest skill would be set to a starter...

Michael Fredrickson
  • 36,839
  • 5
  • 92
  • 109
1

There's some minor confusion over the use of weight, as I'm assuming you you're not doing this on a per-unit basis. You may want to extract out ranges to another table, then use the id's there instead of a numeric weight.

In any case, here's a query that should work for all RDBMSs

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

The inner query should return null (thus setting starter true) if:

  1. There are no other players at the school
  2. There are no players at the same school, in the same weight class
  3. There are no players with a higher skill level, for the same school and weight class
Clockwork-Muse
  • 12,806
  • 6
  • 31
  • 45
  • I am getting an error when trying to set the alias inside of the update. How do I do this for SQL compact server 3.5? I found a question similar, but I was not able to figure it out off of this. http://stackoverflow.com/questions/4981481/how-to-write-update-sql-with-table-alias-in-sql-server-2008 – Meowbits Mar 24 '12 at 00:37
  • @Meowbits - You need to remove the `as`, which apparently are not agnostic – Clockwork-Muse Mar 26 '12 at 16:05
  • I was told that the compact version of SQL does not support a lot of features... apparently that is why I am getting errors. Thanks for the help though, appreciate it! – Meowbits Mar 26 '12 at 22:27