0

I have a table which looks like this:

ID | SerialNumber | SomeData
---|--------------|---------
1  | 1            | abc
2  | 1            | def
3  | 1            | ghi
4  | 1            | jkl
5  | 2            | mno
6  | 2            | pqr

Now i want a query that returns 'SomeData' for every n'th ID of every serial number(if that n'th ID exists for that serial number). So if n=2, the output i'd like is:

ID | SerialNumber | SomeData
---|--------------|---------
2  | 1            | def
6  | 2            | pqr

I think this is already half the solution, but i can't figure out how to make it do what i want it to: How to find 11th entry in SQL Access database table?

KobeFl
  • 17
  • 8

1 Answers1

0

MS Access is a lousy database for this sort of thing. You can enumerate the values with a subquery and then use that for the selection:

select t.*
from (select t.*,
             (select count(*)
              from <your table> as t2
              where t2.serialnumber = t.serialnumber and t2.id <= t.id
             ) as seqnum
      from <your table> as t
     ) as t
where seqnum = 2;

The middle subquery is not actually needed. You could put the correlated subquery in the where clause. I find this form clearer in intent.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786