CODEWITHSUNDEEP
sqldatabasems-access
Developer
Developer

Reputation: 26213

SQL Access db - select every third row from database

How can I select every thrid row from the table?

if a table has 

1
2
3
4
5
6
7
8
9

records it should pick up 3, 6,9 record. regards less what their data is.

Upvotes: 0

Views: 3207

Answers (2)

Richy321
Richy321

Reputation: 410

If its SQL you could use the row_number and over commands. see this, then where rownumvar % 3 =0 but not sure if that works in access.

Or you could put the table into a recordset and iterate through checking the index for % 3=0 if your using any kind of code.

How about a Count() on a field that has unique members. (id?) then % 3 on that.

Upvotes: 0

gbn
gbn

Reputation: 432301

Modulo is what you want...

Assuming contiguous values:

SELECT *
FROM Mytable
WHERE [TheColumn] Mod 3 = 0

And with gaps

SELECT *
FROM Mytable
WHERE DCount("TheColumn", "table", "TheColumn <= " & [TheColumn]) Mod 3 = 0

Edit: To exclude every 3rd record, ...Mod 3 <> 0

Upvotes: 5

Related Questions