Finding every Nth record in a database with a non-incremental index.

[stubarny]

Hello,

 

I have a database of customers with the following structure:

customer_index, customer_name, customer_timestamp

 

Please note the values in field "customer_index" are not incremental.

 

I need to create a webpage with a directory of customer names.

 

This will consist of 120 "name-ranges" e.g:

 

Ace, Peter - Beta, Sam

Brains, Ian - Butts, Ryan

Cripps, Pete - Custard, Brian

etc..

 

To do this I will count the number of customer_index's using sql, then order the database alphabetically using "customer_name" and find every Nth record (the total number of records divided by 120). Please can you tell me how to do this efficiently - my first thought is to create 240 sql statements (using LIMIT) that find the first and last record of each of the 120 name-ranges but is there a simpler way of doing this (e.g. by using 1 sql statement?)

 

Thanks,

 

Stu

[dzelenika]

is this what you need:

<?php
$total = mysql_num_rows($result);
for ($i = 0; $i < $total; $i+= $total/120) 
{
    echo $row['customer_name'] . "<br />\n";
}
?>

[kickstart]

Hi

 

Following is one way to do it in SQL:-

 

set @numa = -1;
set @numb = -1;
SELECT a.customer_name AS StartRangeName, b.customer_name AS EndRangeName, a.customer_index AS StartRangeIndex, b.customer_index AS EndRangeIndex, a.PostedSequence, b.PostedSequence, a.PostedSequence DIV 120
FROM (SELECT customer_index, customer_name, customer_timestamp, @numa := @numa + 1 AS PostedSequence
FROM Customers
ORDER BY customer_name) a
INNER JOIN (SELECT customer_index, customer_name, customer_timestamp, @numb := @numb + 1 AS PostedSequence
FROM Customers
ORDER BY customer_name) b
ON a.PostedSequence DIV 120 = b.PostedSequence DIV 120
WHERE a.PostedSequence % 5 = 0 AND b.PostedSequence % 120 = 119

 

Down side of this is that it ignores the last page unless it has exactly 120 items on it.

 

Coping with the last page:-

 

set @numa = -1;
set @numb = -1;
set @numc = -1;
SELECT Sub2.PageNo, Sub3.customer_name AS StartRangeName, Sub3.customer_index AS StartRangeIndex, Sub4.customer_name AS EndRangeName, Sub4.customer_index AS EndRangeIndex
FROM (SELECT PageNo, MAX(PostedSequence) AS MaxPostedSequence, MIN(PostedSequence) AS MinPostedSequence
FROM (SELECT customer_index, customer_name, customer_timestamp, @numa := @numa + 1 AS PostedSequence, (@numa DIV 120) AS PageNo
FROM Customers
ORDER BY customer_name) Sub1
GROUP BY PageNo) Sub2
INNER JOIN (SELECT customer_index, customer_name, customer_timestamp, @numb := @numb + 1 AS PostedSequence
FROM Customers
ORDER BY customer_name) Sub3 ON Sub3.PostedSequence = Sub2.MinPostedSequence
INNER JOIN (SELECT customer_index, customer_name, customer_timestamp, @numc := @numc + 1 AS PostedSequence
FROM Customers
ORDER BY customer_name) Sub4 ON Sub4.PostedSequence = Sub2.MaxPostedSequence
ORDER BY PageNo

 

All the best

 

Keith

[fenway]

I'm getting to this rather late -- but why can't you simply enumerate the rows and use HAVING to pull out the ones mod 120?  Yes, the "end" is a boundary case, but it's easy to find the "last" one.

[kickstart]

Hi

 

Probably could. I was trying to come up with SQL that would do it in one go without more SQL or dealing with multiple rows to get the other end of the range (ie using MOD 120 on the row number or the row number -1 would give 2 rows to process for each set).

 

The SQL I knocked up could be pretty much executed and the results output directly.

 

Which is possibly a bit OTT.

 

All the best

 

Keith

[stubarny]

Thanks guys, much appreciated.

 

"I'm getting to this rather late -- but why can't you simply enumerate the rows and use HAVING to pull out the ones mod 120?  Yes, the "end" is a boundary case, but it's easy to find the "last" one."

 

Please may I ask how I would go about doing this? (sorry I have googled it but I can't get my head around it)

 

Thanks for your help,

 

Stu

[fenway]

Oh -- simply that you can use a user-variable to "number" each row, and then use "HAVING @counter % 120 = 0" to get the edges.

[dzelenika]

is this what you need:

<?php
$total = mysql_num_rows($result);
for ($i = 0; $i < $total; $i+= $total/120) 
{
    echo $row['customer_name'] . "<br />\n";
}
?>

 

I droped: mysql_data_seek($result,$i);

[stubarny]

Oh -- simply that you can use a user-variable to "number" each row, and then use "HAVING @counter % 120 = 0" to get the edges.

 

LOL let me have a try...

 

Set @counter = -1;
SELECT customer_index, customer_name, customer_timestamp, @counter := @counter + 1 FROM Customers HAVING @counter % 120 = 0

 

Am I close? :-)

 

Stu

 

p.s. please could you tell me what the "%" in "% 120 = 0" does?

[kickstart]

Hi

 

That is close.

 

What that will do is give you every 120th record, starting from the first record. That is basically what the code I provided is doing, except mine also gets the record before the next 120th record in a column.

 

The % is modulus, ie, the remainder after division. So 121 % 120 is 1.

 

All the best

 

Keith

[fenway]

You could include both in your HAVING clause.

[stubarny]

Thanks guys,

 

Please could you tell me if I can nest arguments in a HAVING clause like this?:

 

Set @counter = -1;
SELECT customer_index, customer_name, customer_timestamp, @counter := @counter + 1 FROM Customers HAVING ((@counter % 120 = 0) OR (@counter % 120 = 119))

 

Many thanks,

 

Stu

[fenway]

Not sure that I would call that "nesting" -- you can use the same expression logic that applies in a WHERE clause.

[stubarny]

Thanks Fenway,

 

Is the @counter expression part of the php code like below or is it embedded somehow into the SQL query?

 

Set @counter = -1;
$query ="SELECT customer_index, customer_name, customer_timestamp, @counter := @counter + 1 FROM Customers HAVING ((@counter % 120 = 0) OR (@counter % 120 = 119))";
$result=mysql_query($query)  
or die ("Query failed.");

 

Thanks,

 

Stu

[fenway]

You can initialize your user variable with a JOIN.

[kickstart]

You could include both in your HAVING clause.

 

You could, but then you land up with 2 rows for each page range, rather than a single row with a column for each of the start and end points of the range.

 

If you want to use HAVING and have 2 rows:-

 

$query ="Set @counter = -1; SELECT customer_index, customer_name, customer_timestamp, @counter := @counter + 1 FROM Customers HAVING ((@counter % 120 = 0) OR (@counter % 120 = 119))";
$result=mysql_query($query) or die ("Query failed."); 

 

Note that the above won't give you the end of the last page unless the total number of records is exactly divisible by 120.

 

All the best

 

Keith

[fenway]

I thought that's what I said -- and I still prefer to set the user variable in the same statement.

 

In terms of the last row, just UNION it in if it's not % 120.

[kickstart]

In terms of the last row, just UNION it in if it's not % 120.

 

Can do, but then you land up with a UNIONed statement which is having to do a JOIN from a select to get the MAX and a select to get the details, or instead unioning against a SELECT with an ORDER BY and a LIMIT clause.

 

It is 6 of one and half a dozen of the other. But personally I prefer the single query for both ends of the range on a row.

 

It is a pity that you can't put the sequence number generating select in a VIEW (either SET before or within a subselect). If you could this would simplify my code dramatically.

 

All the best

 

Keith

[fenway]

Oh, I agree, it's ugly.

[stubarny]

out of curiosity - if we use a calculated column (@counter := @counter + 1) is this going to become slow for larger databases (e.g. 10 million records)?

[fenway]

out of curiosity - if we use a calculated column (@counter := @counter + 1) is this going to become slow for larger databases (e.g. 10 million records)?

You could always cache the resulst.

[The Little Guy]

This worked on a similar database for me. All you need to do is change customer_table_name_here to your table name.

 

set @row = 0;
SELECT * 
FROM ( 
    	SELECT @row := @row +1 AS rownum, customer_index, customer_name
    	FROM ( 
		SELECT @row :=0
) r, customer_table_name_here order by customer_name
) ranked WHERE rownum % 120 in(1,0);

 

<?php
$sql = mysql_query("query from above");
$c = 0;
while($row = mysql_fetch_assoc){
if($c == 0)
	echo "<a href="">".$row['customer_name']." - ";
if($c == 1){
	echo $row['customer_name']."</a><br />";
	$c = -1;
}
$c++;
}
if($c == 0);
echo "</a>";
?>