1

So I have the following database format:

+------++------+
| ID   | Value |
+------++------+ 
| 1    |   5   |
| 1    |   2   |
| 1    |   8   |
| 1    |   1   |
| 2    |   2   |
| 2    |   3   |
| 2    |   6   |
| 2    |   10  |
| 3    |   1   |
| 3    |   2   |
| 3    |   5   |
| 3    |   3   | 
| 3    |   5   |
+------++------+

And I am trying to output the top 3 values of each ID. Like:

    +------++------+
    | ID   | Value |
    +------++------+ 
    | 1    |   8   |
    | 1    |   5   |
    | 1    |   3   |
    | 2    |   10  |
    | 2    |   6   |
    | 2    |   3   |
    | 3    |   5   |
    | 3    |   5   |
    | 3    |   3   |
    +------++------+

Is this possible to do in MS Access using SQL?

Stefan V
  • 11
  • 2
  • 2
    This looks like a top N per group Access question. [This question](https://stackoverflow.com/questions/41220690/top-n-records-per-group-sql-in-access) may be helpful. – Tim Biegeleisen Aug 09 '18 at 09:30

1 Answers1

2

You need correlation subquery :

select t.*
from [table] t
where value in (select top 3 t1.value
                from [table] t1
                where t1.id = t.id
                order by t1.value desc
               ) order by ID asc, value desc;
Santosh
  • 12,175
  • 4
  • 41
  • 72
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52