I have multiple records for a set number of people, 4 records each,I need to extract only the top 3 records for each person...can someone tell me how to do this?
Use the TOP N select option in a subquery. Here is an example I did in Access2000:
SELECT tblGroupByTest.Group, tblGroupByTest.Number
FROM tblGroupByTest
WHERE tblGroupByTest.GroupID IN
(SELECT TOP 3 tblGBT.GroupID
FROM tblGroupByTest AS tblGBT
WHERE tblGroupByTest.Group = tblGBT.Group
ORDER BY tblGBT.Group, tblGBT.Number DESC)
ORDER BY tblGroupByTest.Group, tblGroupByTest.Number DESC;
The key to making this work is the WHERE clause in the Subselect. By setting it equal to the outside Group you force it to evaluate for every group, not just once overall which is what TOP N normally returns. GroupID is a primary key which ensures that there are no ties. If I sorted by Group only then the Subselect would bring them all back because it brings back all ties.
In your case, within the Subselect you would want to sort by your date/time field DESC so you get the newest ones coming back to you. The main sort is independent of the Subselect sort.
In the example above, I have data like the following in the table named tblGroupByTest:
Group Number
Andy 100
Andy 200
Andy 300
Andy 400
Andy 500
Andy 600
Andy 700
Gary 10
Gary 20
Gary 30
Gary 40
Gary 50
Gary 60
Leslie 1
Leslie 2
Leslie 3
Leslie 4
Leslie 5
Leslie 6
Leslie 7
Leslie 8
Mark 1000
Mark 2000
Mark 3000
Mark 4000
Mark 5000
Mark 6000
Mark 7000
Mark 8000
Mark 9000
Mark 10000
Mark 11000
Mark 12000
The Query returns data like the following:
Group Number
Andy 700
Andy 600
Andy 500
Gary 60
Gary 50
Gary 40
Leslie 8
Leslie 7
Leslie 6
Mark 12000
Mark 11000
Mark 10000
SELECT table1.member, table1.time
FROM Table1
WHERE table1.id IN
(SELECT TOP 3 tblgbt.id
FROM table1 AS tblgbt
WHERE table1.time = tblgbt.time
ORDER BY tblgbt.member, tblgbt.time DESC)
ORDER BY table1.member, table1.time DESC;
my table:
ID member time
1 deanna 17.650
2 deanna 17.880
3 deanna 16.980
4 deanna 15.880
5 ashley 16.880
6 ashley 17.560
7 ashley 18.980
8 ashley 15.654
I'm still getting all the records not just 3 of each..what'd I do??
SELECT Table1.member, Table1.time
FROM Table1
WHERE (((Table1.ID) In
(SELECT TOP 3 tbl.id
FROM table1 AS tbl
WHERE table1.member = tbl.member
ORDER BY tbl.member, tbl.time DESC)))
ORDER BY Table1.member, Table1.time DESC;
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.