selecting every 5th row form a table

Forum Moderators: open

Message Too Old, No Replies

selecting every 5th row form a table

mini

8:43 am on Jan 3, 2014 (gmt 0)

Hello,

I have a table called priest and it has only two fields - priestid and priestname.

I want to pick up the rows where priestid=1, 5, 9, 13, 17,..............till the end of the table

That means I want to pick up every 5th row (difference=4).

In know the statement :

select * from priest where priestid in (1,5,9)

But I want mysql to pick up on its own till the end of the table......I am stuck with this.

The following has not worked either:
$r=1;
$e=1;
while ($e<51)
{
$result = mysql_query("SELECT * FROM priest where priestid limit $r,1");
$r=$r+4;
}

Please help !

graeme_p

9:07 am on Jan 3, 2014 (gmt 0)

Possible approaches:

1) check the table length (which is fast in MySQL AFAIK) and using that to generate large IN clause . Disadvantage: if your table is large, the IN clause will be too - but as your result set will be just as long, it should not be a problem.

2) Use a user defined function in the WHERE clause. Disadvantage: probably requires a full table scan, but as your query will return a quarter of the table, that should not be a problem either.

3) Just iterate over "SELECT * FROM priest" and discard the unwanted rows. Disadvantage: returns a much larger result set than you need.

Incidentally, your do not want every 5th row, you want every 4th row starting from the first.

Why on earth do you need to do this? Very curious.

phranque

9:13 am on Jan 3, 2014 (gmt 0)

you can do this using modulo operation:
http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_mod

something like:

select * from priest where MOD(priestid - 1, 4) = 0

mini

9:58 am on Jan 3, 2014 (gmt 0)

Thanks a lot... The mod function worked.

One more query.......if I leave the IDs aside and want to show the priest name of the first row, then priestname of the 5th row, then priestname of the 9th row of the table and so on (irrespective of the IDs), how can it be done?

I have tried this with limit and offset but it did not work...

topr8

11:19 am on Jan 3, 2014 (gmt 0)

what do you mean by: .if I leave the IDs aside ?

... i assume you don't just mean changing the select statement to just SELECT priestname FROM etc

phranque

12:32 pm on Jan 3, 2014 (gmt 0)

SELECT priestname FROM priest WHERE MOD(priestid - 1, 4) = 0

graeme_p

7:00 am on Jan 6, 2014 (gmt 0)

OK, silly me. talking about user defined functions when a built in one will work fine.

Full table scan, of course, but if the table is small enough to pull out a quarter of its rows in one query that cannot matter.

phranque

7:06 am on Jan 6, 2014 (gmt 0)

and we're assuming the priestid column is indexed