ma******@gmail.com wrote:
Hello,
Post Table Schema and Sample entries in your table
M A Srinivas
un******@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
