homepage Welcome to WebmasterWorld Guest from 54.211.95.201
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
selecting every 5th row form a table
mini




msg:4634775
 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




msg:4634776
 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




msg:4634777
 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




msg:4634791
 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




msg:4634809
 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




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

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




msg:4635445
 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




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

and we're assuming the priestid column is indexed

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved