Need 'distributed' sort... 1

[admoore]

I'll do my best to describe what I need; hopefully, my description is clear.

I have a table containing a group of records with 2 fields upon which i need to sort.
record_no (integer and primary key)
and
cust_id (varchar - data set contains duplicates)

In a nutshell I need a result sorted such the I get the record with the lowest record_no of each cust_id, then the second lowest record_no of each cust id, etc, meaning that the result will have the duplicate cust_id's evenly distributed throughout the result, In other words, if the cust_id of the lowest record_no was "Al", and there were 300 distinct cust_id's the result would position "Al" as the first record, and again at the 301st position and so on until the result was complete. The actual number of occurrences for each distinct cust_id varies.

I hope I have described my predicament clearly. I would expect to abandon the original record_no in lieu of an auto_increment integer in the result table.

Can I do this in MySQL or do I need to loop a number of queries in PHP to do this?

Thanks for suggestions,

-Allen M.

 

[jpadie]

difficult to guess what you mean.

but why not this

select * from tableName
order by   cust_id ASC, record_no ASC

 

[admoore]

Maybe this will help illustrate what I need...:

 

[admoore]

slight error in my illustration towards the end; but, hopefully it better defines what I need...

 

[jpadie]

hmm. unless the cust_id is a defined set then it needs to be treated as arbitrary.

it looks like you are saying this in pseudo code

establish a holding array
establish a results array
get the set of distinct custIDs
LOOP
 iterate the custIDs retrieving the minimum value for recordID that is not in the holding array.
 add that value to the holding array
 add that row to the results array.
ENDLOOP

you could do it in php (although this would be slow).
I can't quite fathom the business logic for such a display. Perhaps if we knew that we could suggest ways to change your architecture to make it straight forward.

in php the code would look something like this. not tested of course. I have used a mysql table rather than an array as I suspect that this will be quicker.

<?php 
$pdo = new PDO(....connect ....);
$statement = $pdo->prepare('select distinct cust_id from tableName order by cust_id ASC');
$statement->execute();
$custIDs = array();
while ($row = $statement->fetchObj()):
	$custIDs[] = $row->cust_id;
endwhile;

$pdo->exec('create table myTemp like tableName');
$pdo->exec('create index y on myTemp (cust_id)');
$pdo->exec('create index z on myTemp (record_id)');

$statement = $pdo->prepare(
'INSERT INTO myTemp 
	(
	SELECT 		t.* 
	FROM 		tableName t
	WHERE 		t.cust_id = :cust_id
	AND		t.record_id > (SELECT MAX(m2.record_id) FROM myTemp m2 where m2.cust_id=:cust_id)
	ORDER BY	t.record_id ASC
	LIMIT 		1
	)
');
if($statement === false) die(print_r($pdo->errorInfo(), true));
do{
	$count = 0;
	foreach($custIDs as $custID):
		$result = $statement->execute(array(':cust_id' => $custID));
		if($result === false) die(print_r($statement->errorInfo(), true));
		$count += $statement->rowCount();
	endforeach;
} while ($count > 0);

$statement = $pdo->prepare('select * from tableName');
$statement->execute();
echo '<pre>';
while($row = $statement->fetchAssoc()):
	print_r($row);
endwhile;
echo '</pre>';
$pdo->exec('drop table myTemp');
?>

you could probably also do this as a stored procedure with a temp table but beware that you cannot reference a temp table twice in an insert ... select statement. so you'd need instead to hold the last entered record ID as a variable for reach customerID. or perform the select max() before the insert select and store the result as a variable.

 

[admoore]

It is an odd request to be sure... The explanation is that the individual records represent target households for marketing and the cust_id represents the "source" of each target record. The need to spread them out as described arised from budgetary constraints. When the marketing list is trimmed down to meet the budget the desire is to end up with an qual number of target records from each source.

 

[jpadie]

I'd guess excel may be a good tool for that then. a pivot around the list might be the most flexible way to approach it, particularly if the intended output is to be used for factoring budgets.