bitt3n
asked on
how do I formulate a query that limits results to X records for each value of a given column?
Given a query of the following form
SELECT shop_id, date FROM products ORDER BY shop_id, date
what is the best way to limit the number of results per shop_id? That is, instead of limiting the query to, for example, 10 results, I want retrieve the last 10 dates for each shop_id
From
http://mysql.bigresource.com/Track/mysql-tK0A4nFa/
I tried
SELECT p1.shop_id, p1.date, count(*) rank FROM
products p1 LEFT JOIN
products p2 ON
p1.shop_id=p2.shop_id AND
p1.date <= p2.date GROUP BY
p1.shop_id, p1.date ORDER BY
p1.shop_id,rank
but even indexing the products table on shop_id and date, this query times out, so clearly I'm doing something wrong. (Also I'll have to add a 'HAVING (rank <= X)' for the actual limit once I get it working.) It's quite possible this is not even close to what I want.
SELECT shop_id, date FROM products ORDER BY shop_id, date
what is the best way to limit the number of results per shop_id? That is, instead of limiting the query to, for example, 10 results, I want retrieve the last 10 dates for each shop_id
From
http://mysql.bigresource.com/Track/mysql-tK0A4nFa/
I tried
SELECT p1.shop_id, p1.date, count(*) rank FROM
products p1 LEFT JOIN
products p2 ON
p1.shop_id=p2.shop_id AND
p1.date <= p2.date GROUP BY
p1.shop_id, p1.date ORDER BY
p1.shop_id,rank
but even indexing the products table on shop_id and date, this query times out, so clearly I'm doing something wrong. (Also I'll have to add a 'HAVING (rank <= X)' for the actual limit once I get it working.) It's quite possible this is not even close to what I want.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think you wish to have a mysql only solution but it is simple to do with a programming language like PHP and it makes it efficient (1 pass through the table), only it needs the whole data set sent to PHP.
The below works for me: change **** to your user and pass and change "test" to you database name and run in a local webserver ... or command line.
<?PHP
$filename=basename($_SERVE R['PHP_SEL F']);
#open first database
$HOST='localhost';$USER='* ***';$PASS ='****';
$database_link1 = mysql_connect($HOST, $USER, $PASS) OR die ("$filename: Connecting problem: ".mysql_errno()." .". mysql_error() .". tis all.");
mysql_select_db("test", $database_link1);
$st = mysql_query("SELECT shop_id, date FROM products ORDER BY shop_id, date desc");
$counter = 0;
$prev_shop=-1;
while (list($shop_id,$date) = mysql_fetch_row($st)) {
$counter = ($shop_id == $prev_shop?$counter+1:0);
if ($counter < 11) {
$final_results{$shop_id.": ".$counter } = $date;
}
$prev_shop = $shop_id;
}
foreach ($final_results as $key=>$value) {
list($shop_id, $counter) = explode(":",$key);
print "$counter $shop_id = $value<BR>\n";
}
?>
The below works for me: change **** to your user and pass and change "test" to you database name and run in a local webserver ... or command line.
<?PHP
$filename=basename($_SERVE
#open first database
$HOST='localhost';$USER='*
$database_link1 = mysql_connect($HOST, $USER, $PASS) OR die ("$filename: Connecting problem: ".mysql_errno()." .". mysql_error() .". tis all.");
mysql_select_db("test", $database_link1);
$st = mysql_query("SELECT shop_id, date FROM products ORDER BY shop_id, date desc");
$counter = 0;
$prev_shop=-1;
while (list($shop_id,$date) = mysql_fetch_row($st)) {
$counter = ($shop_id == $prev_shop?$counter+1:0);
if ($counter < 11) {
$final_results{$shop_id.":
}
$prev_shop = $shop_id;
}
foreach ($final_results as $key=>$value) {
list($shop_id, $counter) = explode(":",$key);
print "$counter $shop_id = $value<BR>\n";
}
?>
ASKER
That's an interesting idea. However, I definitely want a MySQL solution if at all possible.
ASKER
thanks, the user variables solution from the link you posted works well. I have a followup question that I have posted here https://www.experts-exchange.com/questions/26850106/how-do-I-fix-this-MySQL-query-employing-user-variables.html
ASKER
the UNION approach would work, but it seems like I'd need first to retrieve the current shop_ids, and then formulate the query I want using PHP to cycle through the shop_ids in order to create what might well be a very long list of UNIONs depending on the number of shop_ids. This seems less than elegant.
the user variables approach that guy recommends might work better.. I'll report back once I've learned more about it and tried it out. (I've never used user variables before.)