SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Evangelist jkh1978's Avatar
    Join Date
    May 2001
    Location
    Northern Virginia
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    selection criteria

    Say I have this table

    Year, Player, Home Runs hit

    How can I do a select to get a list of players that hit over 40 home runs in two consecutive years?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    join the table to itself, off by one year
    Code:
    select second.year, second.player
      from yourtable as second
    inner 
      join yourtable as first
        on second.player = first.player
       and second.year = first.year + 1
     where second.homers >= 40
       and first.homers >= 40
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tell me you don't have spaces in your field names!!!!

    </rant>



    G

  4. #4
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't think that'd do it, r937, because what if the records go further back than two years? It would probably be better to start an extra field to hightlight these players and stay on top of it in the future ......


  5. #5
    SitePoint Evangelist jkh1978's Avatar
    Join Date
    May 2001
    Location
    Northern Virginia
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    of course I do not have spaces in my field names.


    The database will have more than just the two years, it will go back 100 years. For each player, it will only have a few years for each player.

    I do not want to manually add a new field for this, because I might want to change the threshold to between 15 - 25, and pick three years instead of 2.

  6. #6
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tricky! I suspect you're gonna have to write a function in whatever the language of choice for you is ..... if it were me then that's what I'd do. Shouldn't be too hard, but not as tidy as a nice SQL query. Oh well ...

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    did either of you guys actually try the query i suggested? that's what i would do, before resorting to functions and extra columns and stuff

    the self-join is designed to work on a table containing any number of years, and it will return the second year of any two consecutive years

    i didn't test it myself but i've written that kind of query before

    besides, jkh1978, you have the table, i'd have to build it

    by the way, if you want to test three years, you need a three-way self-join

    rudy

  8. #8
    SitePoint Evangelist jkh1978's Avatar
    Join Date
    May 2001
    Location
    Northern Virginia
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry r937. I should try out your test. I only doubted it since others on this chain gave reason for doubt. I do not have the database built yet, but wanted to make sure I could do such queries before I went out to obtain the data.

  9. #9
    SitePoint Evangelist jkh1978's Avatar
    Join Date
    May 2001
    Location
    Northern Virginia
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    would you be so kind to show me how to do it for three teams, and how the joins work then?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    first, create the test data

    player year homers
    Dick 1997 46
    Dick 1998 26
    Dick 1999 45
    Dick 2000 25
    Dick 2001 42
    Dick 2002 22
    Harry 1953 45
    Harry 1955 45
    Harry 1957 45
    Moe 1937 21
    Moe 1938 22
    Moe 1939 23
    Tom 1987 44
    Tom 1988 38
    Tom 1989 39
    Tom 1990 40
    Tom 1991 41

    now, run the original query, consecutive 40-homer years

    select second.player, second.year, second.homers
    , first.year, first.homers
    from playerhomers second
    inner
    join playerhomers first
    on second.player = first.player
    and second.year = first.year + 1
    where second.homers >= 40
    and first.homers >= 40
    order by 1,2

    Tom 1991 41 1990 40

    change the criterion to consecutive 20-homer years, and you get more results

    select second.player, second.year, second.homers
    , first.year, first.homers
    from playerhomers second
    inner
    join playerhomers first
    on second.player = first.player
    and second.year = first.year + 1
    where second.homers >= 20
    and first.homers >= 20
    order by 1,2

    Dick 1998 26 1997 46
    Dick 1999 45 1998 26
    Dick 2000 25 1999 45
    Dick 2001 42 2000 25
    Dick 2002 22 2001 42
    Moe 1938 22 1937 21
    Moe 1939 23 1938 22
    Tom 1988 38 1987 44
    Tom 1989 39 1988 38
    Tom 1990 40 1989 39
    Tom 1991 41 1990 40

    modify the query to select three consecutive years of 20+ homers

    select third.player, third.year, third.homers
    , second.year, second.homers
    , first.year, first.homers
    from ( playerhomers third
    inner
    join playerhomers second
    on third.player=second.player
    and third.year = second.year + 1 )
    inner
    join playerhomers first
    on second.player = first.player
    and second.year = first.year + 1
    where third.homers >= 20
    and second.homers >= 20
    and first.homers >= 20
    order by 1,2

    Dick 1999 45 1998 26 1997 46
    Dick 2000 25 1999 45 1998 26
    Dick 2001 42 2000 25 1999 45
    Dick 2002 22 2001 42 2000 25
    Moe 1939 23 1938 22 1937 21
    Tom 1989 39 1988 38 1987 44
    Tom 1990 40 1989 39 1988 38
    Tom 1991 41 1990 40 1989 39


    rudy


Bookmarks

Posting Permissions

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