Re: How To Find Baseball Consecutive Hit Streak With A Select Statement

I think analytical functions might help you out (lead/lag). Will enable you to find the preceeding/next result in the set.

"Greg Teets" <gteets_at_cinci.rr.com> wrote in message news:ca75pvojul6n4khrc57qfui7ld5c7mgqsg_at_4ax.com...
> Not a homework assignment.
>
> I know how to do it that way. My question was is their a direct way
> to do it in a SELECT statement with any builtin function.
>
> Thanks for your response.
>
>
>
>
> On Sun, 19 Oct 2003 07:01:42 -0400, Clifford Buetikofer
> <rcbuetikofer_at_comcast.net> wrote:
>
> >Greg Teets wrote:
> >> I've never known how to do this in a SELECT statement and I only think
> >> about this when I'm watching a baseball game. Well, I've been
> >> watching the baseball playoffs, so here we go:
> >>
> >> A table has columns for player name, game number, and the number of
> >> hits that player had in that game. Can I find out which player had
> >> the longest streak of games with a hit in each game.
> >>
> >> Obviously a GROUP BY player_name, but how can I write a clause to
> >> calculate the number of consecutive games that a player had hits
> >> greater than 0? Note that a player may not have played in every game
> >> during a season.
> >>
> >> Thanks.
> >>
> >
> >Sounds like homework... :)
> >
> >Load them into an array in ascending player,game order. Then just loop
> >through using n and (n+1) logic keeping track of the player and whether
> >or not the game counter should increment or restart counting within each
> >player.
> >
> >What happens if a player never gets a hit in any game?
> >
> >Cliff
> >
>
Received on Mon Oct 20 2003 - 10:12:15 CEST