How do I filter/limit the number rows in the query output ?

[jeddiki]

Hi,
I want to get a subset from my table that includes rows that have an item (cb_id) with a unique ip address ( ip_adr). To be in the subset there should be at least two rows and if there are more than four rows then the rows above the forth should be filtered out.

eg. table contains:

EBFORTUNE, IP add: 55.167.889.182
EBFORTUNE, 78.167.109.182
FEDYDE, : 85.106.68.197
FEDYDE, 85.176.68.227
FEDYDE, 89.106.68.127
FHHHH45,78.167.109.182 // only one - not enough - filter out
P97GFRH, 56.107.9.182 // only one - not enough - filter out
RSEDE38M, 78.167.17.182
RSEDE38M, 78.167.109.182
RSEDE38M, 23.167.109.182 // 7 the extra 3 rows must be filtered out
RSEDE38M, 17.167.19.2
RSEDE38M, 712.17.39.182
RSEDE38M, 78.16.143.182
RSEDE38M, 78.19.109.102
GGG72FHH, 178.7.109.18 // only one - not enough - filter out


Then the result I want to get is (min 2, max 4):

EBFORTUNE,55.167.889.182
EBFORTUNE, 78.167.109.182
FEDYDE, 85.106.68.197
FEDYDE,85.176.68.227
FEDYDE, 89.106.68.127
RSEDE38M, 17.167.19.2
RSEDE38M, 712.17.39.182
RSEDE38M, 78.16.143.182
RSEDE38M, 78.19.109.102


I am quite close to this but
I just need to take out the extra entries where the number
of rows exceeds the ceiling.

$days7ago = $today-604800; // Also data must be within last 7 days.
 
$min_hits = 2;
$ceiling = 4;
 
echo "<br>Min_hits: $min_hits, Ceiling: $ceiling<br>Having sql:<br>";
 
$min_hits = $min_hits-1; // because I use the less than operator.
 
$sql = "SELECT DIC.ip_adr, COUNT(*) AS howmany FROM (SELECT DISTINCT ip_adr, cb_id FROM cb_promo WHERE link_dt BETWEEN $days7ago and $today)
 AS DIC    GROUP BY DIC.cb_id HAVING COUNT(*) > $min_hits";
 
$result = mysql_query($sql)    or die("could not find PROMO". mysql_error());  
while( $row = mysql_fetch_assoc($result)) {
 print_r($row);
 echo "<br>";
} 

Result:

Min_hits: 2, Ceiling: 4
Having sql:
Array ( [ip_adr] => 478.167.109.182 [howmany] => 6 )
Array ( [ip_adr] => 78.14.109.162 [howmany] => 2 )

So just need to get rid of (count-ceiling) rows from the array
but not sure how to do that one.

Any ideas on how I can complete my filtering ?

Either by expanding the query or by processing the resulting array
that I already have.

Thanks

[dlite922]

This would be answered faster if asked in a MySQL forum.

Dan