Welcome to WebmasterWorld Guest from 174.129.135.89

Forum Moderators: open

Message Too Old, No Replies

selecting every 5th row form a table

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

5+ Year Member



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 !
9:07 am on Jan 3, 2014 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month



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.
9:13 am on Jan 3, 2014 (gmt 0)

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



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
9:58 am on Jan 3, 2014 (gmt 0)

5+ Year Member



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...
11:19 am on Jan 3, 2014 (gmt 0)

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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
12:32 pm on Jan 3, 2014 (gmt 0)

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



SELECT priestname FROM priest WHERE MOD(priestid - 1, 4) = 0
7:00 am on Jan 6, 2014 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month



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.
7:06 am on Jan 6, 2014 (gmt 0)

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



and we're assuming the priestid column is indexed