471,319 Members | 1,957 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,319 software developers and data experts.

Select and limit results in one table?

jon
Please Excuse the newbie question, but I have not been able to figure
out how to do the following on my own. Assume I have a table called
'reviews' and in that table there are three columns: reviewDateTime,
reviewID, and reviewItem. reviewItem is not unique, the other two are.

What I want to do in pseudo-code is write a select query that will:

for each unique reviewItem select 5 reviewID by reviewDateTime
ascending

Any ideas?

Feb 28 '07 #1
3 5590
Use a subquery to limit the query to the first 5 for the reviewItem based on
ascending reviewDateTime.

Something like this:

SELECT * FROM Table1
WHERE reviewItem IN
(SELECT TOP 5 reviewItem
FROM Table1 AS Dupe
WHERE Dupe.reviewItem = Table1.reviewItem
ORDER BY Dupe.reviewDateTime);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<jo*@jontillman.comwrote in message
news:11**********************@v33g2000cwv.googlegr oups.com...
Please Excuse the newbie question, but I have not been able to figure
out how to do the following on my own. Assume I have a table called
'reviews' and in that table there are three columns: reviewDateTime,
reviewID, and reviewItem. reviewItem is not unique, the other two are.

What I want to do in pseudo-code is write a select query that will:

for each unique reviewItem select 5 reviewID by reviewDateTime
ascending

Any ideas?
Feb 28 '07 #2
jon
On Feb 28, 3:41 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
Use a subquery to limit the query to the first 5 for the reviewItem based on
ascending reviewDateTime.

Something like this:

SELECT * FROM Table1
WHERE reviewItem IN
(SELECT TOP 5 reviewItem
FROM Table1 AS Dupe
WHERE Dupe.reviewItem = Table1.reviewItem
ORDER BY Dupe.reviewDateTime);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<j...@jontillman.comwrote in message

news:11**********************@v33g2000cwv.googlegr oups.com...
Please Excuse the newbie question, but I have not been able to figure
out how to do the following on my own. Assume I have a table called
'reviews' and in that table there are three columns: reviewDateTime,
reviewID, and reviewItem. reviewItem is not unique, the other two are.
What I want to do in pseudo-code is write a select query that will:
for each unique reviewItem select 5 reviewID by reviewDateTime
ascending
Any ideas?
Thanks, that did the trick!

Feb 28 '07 #3
John, there's now a couple of new article on subqueries.

The first one explains the basics:
http://allenbrowne.com/subquery-01.html

The second one give trouble-shooting tips:
http://allenbrowne.com/subquery-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<jo*@jontillman.comwrote in message
news:11**********************@s48g2000cws.googlegr oups.com...
On Feb 28, 3:41 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
>Use a subquery to limit the query to the first 5 for the reviewItem based
on
ascending reviewDateTime.

Something like this:

SELECT * FROM Table1
WHERE reviewItem IN
(SELECT TOP 5 reviewItem
FROM Table1 AS Dupe
WHERE Dupe.reviewItem = Table1.reviewItem
ORDER BY Dupe.reviewDateTime);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<j...@jontillman.comwrote in message

news:11**********************@v33g2000cwv.googleg roups.com...
Please Excuse the newbie question, but I have not been able to figure
out how to do the following on my own. Assume I have a table called
'reviews' and in that table there are three columns: reviewDateTime,
reviewID, and reviewItem. reviewItem is not unique, the other two are.
What I want to do in pseudo-code is write a select query that will:
for each unique reviewItem select 5 reviewID by reviewDateTime
ascending
Any ideas?

Thanks, that did the trick!
Mar 1 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Bob Bedford | last post: by
3 posts views Thread by Sven Reifegerste | last post: by
3 posts views Thread by Wim Roffil | last post: by
11 posts views Thread by wackyphill | last post: by
3 posts views Thread by Tcs | last post: by
7 posts views Thread by Milos Prudek | last post: by
reply views Thread by Steve Crawford | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.