Welcome to WebmasterWorld Guest from 54.160.198.60

Forum Moderators: open

Message Too Old, No Replies

selecting every 5th row form a table

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

Junior Member

5+ Year Member

joined:Aug 17, 2007
posts:91
votes: 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 !
9:07 am on Jan 3, 2014 (gmt 0)

Senior Member from LK 

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

joined:Nov 16, 2005
posts:2523
votes: 37


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)

Administrator

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

joined:Aug 10, 2004
posts:10563
votes: 15


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)

Junior Member

5+ Year Member

joined:Aug 17, 2007
posts:91
votes: 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...
11:19 am on Jan 3, 2014 (gmt 0)

Senior Member

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

joined:Apr 19, 2002
posts:3212
votes: 13


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)

Administrator

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

joined:Aug 10, 2004
posts:10563
votes: 15


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

Senior Member from LK 

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

joined:Nov 16, 2005
posts:2523
votes: 37


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)

Administrator

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

joined:Aug 10, 2004
posts:10563
votes: 15


and we're assuming the priestid column is indexed