Results 1 to 6 of 6

Thread: SQL Purge help

  1. #1
    Join Date
    Aug 2011
    Posts
    34

    Unanswered: SQL Purge help

    Hey Guys,

    I've been asked to write some code for a baseball game where a player has to win multiple games before he can upgrade his team. We've noticed that a player can play both teams and win every game. I need to exclude games where the player has played both teams to win as that is cheating.

    Here is a dumbed down version of my code:
    SELECT
    PLAYER,
    GAME,
    TEAM,
    RANK () OVER (PARTITION BY PLAYER, GAME ORDER BY TEAM) AS rank
    FROM BASEBALL
    WHERE PLAYER = 1315963

    HTML Code:
    Code Output:
    PLAYER	GAME	 TEAM	        RANK
    1315963	1028364	NATIONALS	  1
    1315963	1028368	BRAVES	          1
    1315963	1028368	PHILIES	          2
    1315963	1028372	CARDINALS	  1
    1315963	1028380	JAYS	          1
    1315963	1028382	ORIOLES	          1
    1315963	1028382	YANKEES	          2
    as you can see game 1028368 and 1028382 need to be purged. what do you think the best way to do it is?

    Thanks for the help in advance
    - Using Oracle 11g with Toad v12

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DELETE FROM BASEBALL
       WHERE EXISTS (SELECT *
          FROM BASEBALL AS Z
          WHERE  Z.PLAYER =  BASEBALL.PLAYER
             AND Z.GAME   =  BASEBALL.GAME
             AND Z.TEAM   <> BASEBALL.TEAM)
    
    ALTER TABLE BASEBALL
       ADD CONSTRAINT NEWID
       UNIQUE(PLAYER,GAME)
    This way the problem will STAY fixed!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by nadecian View Post
    what do you think the best way to do it is?
    The best in what sense?
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Pat Phelan View Post
    Code:
    DELETE FROM BASEBALL
       WHERE EXISTS (SELECT *
          FROM BASEBALL AS Z
          WHERE  Z.PLAYER =  BASEBALL.PLAYER
             AND Z.GAME   =  BASEBALL.GAME
             AND Z.TEAM   <> BASEBALL.TEAM)
    ...
    Slight modification might be better.
    Code:
    DELETE FROM baseball AS t
     WHERE EXISTS
           (SELECT *
             FROM  baseball AS z
             WHERE z.PLAYER = t.PLAYER
               AND z.GAME   = t.GAME
               AND z.TEAM   < t.TEAM
           )
    ;
    Last edited by tonkuma; 04-01-13 at 17:38.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by tonkuma View Post
    Slight modification might be better.
    I'd normally agree, but based on:
    Quote Originally Posted by nadecian View Post
    I need to exclude games where the player has played both teams to win as that is cheating.

    <<snip>>

    as you can see game 1028368 and 1028382 need to be purged.
    I assume that the whole game needs to be expunged instead of just one team.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I assume that the whole game needs to be expunged instead of just one team.
    Yes!
    I might jumped to a conclusion.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •