Top 5 records by group in MS Access

Question

The code below returns all records :-

SELECT b.BROKERAGE_NAME, q.VAL_DATE
FROM TBLQUOTESNEW q LEFT JOIN TBLBROKERAGESNEW b ON q.BROKERAGE_ID = b.ID
WHERE q.VAL_DATE Is Not Null

Is it possible to have the top 5 records for each BROKERAGE_NAME returned, where VAL_DATE is descending?

Included

    Brokerage A    02/10/2017
    Brokerage A    02/10/2017
    Brokerage A    02/10/2017
    Brokerage A    02/10/2017
    Brokerage A    01/10/2017
    Brokerage B    06/06/2016

Excluded (as not in top 5)

    Brokerage A    30/09/2017

I was going to suggest using `SELECT TOP 5` & `ORDER BY VAL_DATE DESC`, but that will only work if your dates have a different time range. As it is 2/10/2017 will be first place and 30/09/2017 will be second. — Darren Bartrup-Cook, Oct 05 '17 at 09:49
Review http://allenbrowne.com/subquery-01.html, the Top n per group section — Erik A, Oct 05 '17 at 09:55
Possible duplicate of [Top n records per group sql in access](https://stackoverflow.com/questions/41220690/top-n-records-per-group-sql-in-access) — Erik A, Oct 05 '17 at 09:57
Have to agree with @ErikvonAsmuth. Was looking at how to do it and that was the post I found and I think the Allenbrowne link was the one I used to get the answer in the duplicate. — Darren Bartrup-Cook, Oct 05 '17 at 10:03
@user1936588 Not by me doesn't mean it's not a duplicate. You're supposed to do your research into existing questions posted by others — Erik A, Oct 05 '17 at 10:07
If I was able to find an answer I was able to use, I would have done so, and not posted my question. I had already searched not was not able to adapt any code I found to match what I needed. I apologise for my lack of knowledge on the matter. — bd528, Oct 05 '17 at 10:10
From what I can tell - untested - the adaptation would be `SELECT b.BROKERAGE_NAME, q.VAL_DATE FROM TBLQUOTESNEW q LEFT JOIN TBLBROKERAGESNEW b ON q.BROKERAGE_ID = b.ID WHERE q.VAL_DATE IN (SELECT TOP 5 q1.VAL_DATE FROM TBLQUOTESNEW q1 LEFT JOIN TBLBROKERAGESNEW b1 ON q1.BROKERAGE_ID = b1.ID WHERE b1.BROKERAGE_NAME = b.BROKERAGE_NAME ORDER BY q1.VAL_DATE DESC) ORDER BY b.BROKERAGE_NAME, q.VAL_DATE`. I won't put as answer as I just copied the query from the duplicated link and changed the field and table names. — Darren Bartrup-Cook, Oct 05 '17 at 10:14
@user1936588 If you've found that answer, share an implementation of it and any errors you've found while trying — Erik A, Oct 05 '17 at 10:19

score 1 · Accepted Answer · edited Oct 05 '17 at 10:37

1

SELECT TOP 5 
      b.BROKERAGE_NAME,
      q.VAL_DATE
FROM 
      TBLQUOTESNEW q 
LEFT JOIN 
      TBLBROKERAGESNEW b 
      ON q.BROKERAGE_ID = b.ID
WHERE 
      q.VAL_DATE Is Not Null 
GROUP BY 
      BROKERAGE_NAME 
ORDER BY 
      VAL_DATE DESC`

edited Oct 05 '17 at 10:37

boop_the_snoot

3,209
4
33
44

answered Oct 05 '17 at 09:46

Almantas Bendoraitis

26
1
2

This doesn't return the results as specified in the question. It returns the top 5 brokerages. Not the top 5 VAL_DATE's per brokerage. – bd528 Oct 05 '17 at 09:49
That's because your sample data only has Brokerage A - he's probably done the same as I did and just copy your sample and work with that. Completely ignored that you want it split by brokerage. :) – Darren Bartrup-Cook Oct 05 '17 at 09:51
@DarrenBartrup-Cook - Improved sample data :) – bd528 Oct 05 '17 at 09:55

Top 5 records by group in MS Access

1 Answers1