in reply to Can this be done entirely in MySQL?

If your rows are numbered, you can do this:

select floor(regno/100) "groupby", min(regno),max(regno),min(name),max
+(name) 
from registrations 
group by `groupby`;

[download]

But I presume they're not, in which case you have to roll it on the fly (and I hope you're using 4.1 or better, or the subselect will come crashing down):

set @rownum := 0;
select floor(@rownum  := @rownum + 0.01) "set_num", min(lname),max(lna
+me) 
from (select * from registrants order by lname) reg 
group by "set_num";

[download]

Then of course you do the paged query using LIMIT $set_num*100, 100, which is of course subject to quirks if somebody named Jones registers when you're about to look at the page that used to start with Kerrigan, but that's a separate issue. :-)

Hope this helps!


If God had meant us to fly, he would *never* have given us the railroads.
    --Michael Flanders

Replies are listed 'Best First'.
Re^2: Can this be done entirely in MySQL?
by gam3 (Curate) on Apr 22, 2005 at 03:22 UTC
    If you have an index you can do this without the subselect.

    You can use ignore index if you want the natural order, but the you can't currently create a descending index. 

    create index registrants_lname_asc on registrants (lname ASC);

SET @rownum := 0
SELECT CONCAT(min(@rownum := @rownum + 1), '-',
              max(@rownum), ' (', 
              min(lname), '-', max(lname), ')')
  from registrants use index (registrants_lname_asc)
 group by floor(@rownum/100)

    [download]
    -- gam3
    A picture is worth a thousand words, but takes 200K.
[reply]
[d/l]

In Section Seekers of Perl Wisdom