By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,569 Members | 1,394 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,569 IT Pros & Developers. It's quick & easy.

How To Select a Certain Limited Number of Rows Per ID

P: n/a
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.

Sep 18 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.
Sep 19 '06 #2

P: n/a

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

Sep 19 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.