Posted by unabogie on 09/19/06 16:49

masri999@gmail.com wrote:
> Hello,
>
> Post Table Schema and Sample entries in your table
>
> M A Srinivas
>
> unabogie@gmail.com wrote:
> > I have a table with entries tied to a membership database. The problem
> > is that I want to select a limit of sixteen entries per member, per
> > day, where some members have 16+ entries per day.
> >
> > I have this so far ( which I've simplified for this post)
> >
> > SELECT dbo.members.firstname, dbo.members.lastname,
> > dbo.entries.gameDay
> > FROM dbo.members INNER JOIN
> > dbo.entries ON dbo.members.memberID =
> > dbo.entries.memberID
> >
> > If it's day 5, each member should have 80 total.
> >
> > How can I change this to select only 16 entries for each member, making
> > sure it's 16 per day based on dbo.entries.gameDay?
> >
> > Thanks for your help.

I got a solution from another board, so I'll post it here.

create table members

(
memberID int identity,
firstname varchar(50),
lastname varchar(50)

)

create table entries

(
entryID int identity,
memberID int,
gameDay int
)

insert into members values('Joe', 'Smith')
insert into entries values(1,1)
insert into entries values(1,1)
insert into entries values(1,1)
insert into entries values(1,1)
insert into entries values(1,1)
insert into entries values(1,1)
insert into entries values(1,1)
insert into entries values(1,1)
insert into entries values(1,1)
insert into entries values(1,1)
insert into entries values(1,1)
insert into entries values(1,1)
insert into entries values(1,1)
insert into entries values(1,1)
insert into entries values(1,1)
insert into entries values(1,1)
insert into entries values(1,1)
insert into entries values(1,1)
insert into entries values(1,1)
insert into entries values(1,1)
insert into entries values(1,1)
insert into entries values(1,2)
insert into entries values(1,2)
insert into entries values(1,2)
insert into entries values(1,2)
insert into entries values(1,2)

SELECT *
FROM Members m
INNER JOIN entries e
ON e.MemberID = m.MemberID
WHERE (SELECT COUNT(*)
FROM entries e2
WHERE e2.MemberID = e.MemberID
AND e2.GameDay = e.GameDay
AND e2.EntryID < e.EntryID
) < 16


Thanks

• Next in forum: Re: Help needed with DATETIME Arithmetic
• Prev in forum: Help needed with DATETIME Arithmetic
• Thread view: Re: How To Select a Certain Limited Number of Rows Per ID

[Reply to this message]