Solved

how do I formulate a query that limits results to X records for each value of a given column?

Posted on 2011-02-24
5
304 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:bitt3n
  • 3
  • 2
5 Comments
 
LVL 8

Accepted Solution

by:
ropenner earned 500 total points
ID: 34977710
This works for me (Ubuntu/mysql 5.1.49).  Solution help came from this site:
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

select shop_id, date
from products
where (
   select count(*) from products as p
   where p.shop_id = touch.shop_id and p.date < products.date
) <= 9;


0
 

Author Comment

by:bitt3n
ID: 34984531
unfortunately, even with indices on shop_id and date, that method appears not to be efficient enough for my purposes (at least using MySQL 5.0.51b on OSX).

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.)
0
 
LVL 8

Expert Comment

by:ropenner
ID: 34984865
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($_SERVER['PHP_SELF']);

#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";
}
?>
0
 

Author Comment

by:bitt3n
ID: 34984930
That's an interesting idea. However, I definitely want a MySQL solution if at all possible.
0
 

Author Comment

by:bitt3n
ID: 34988567
thanks, the user variables solution from the link you posted works well. I have a followup question that I have posted here http://www.experts-exchange.com/Database/MySQL/Q_26850106.html
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
count download link and run update query 9 84
showing numeric numbers 2 38
Have issues with Query MySQL 9 72
Optimize the query 5 43
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question