select last x records per x categories

johanimmers

Hi there.

I have a small question. I want to know if my "problem" can be solved in one query (still using MySQL 3.2). This is a simplified dump of the table. An auto-incrementfield, a category and a title. I want the last x records per category. I know it is very easy to solve using a loop and walk trough all categories in PHP. But is there a smarter way to combine some group by and having statements to fix this in one query?

This is the table dump:
[ID, category, title]
01 | 1 | een titel
02 | 1 | nog titel
03 | 3 | titeltje
04 | 2 | zoveelste titel
05 | 1 | titel titel
06 | 2 | meer titel
07 | 2 | nog een titel
08 | 3 | titeldetitel
09 | 3 | titelitelietel
10 | 5 | de titel

This is the list I would like to generate
[ID, category, title]
05 | 1 | titeltitel
02 | 1 | nog titel
01 | 1 | een titel
07 | 2 | nog een titel
06 | 2 | meer titel
04 | 2 | zoveelste titel
09 | 3 | titelitelietel
08 | 3 | tieteldetitel
03 | 3 | titeltje

Any help would be very appreciated.

devinemke

SELECT * FROM table ORDER BY category ASC, ID DESC

bpat1434

don't forget a LIMIT # after, so you return only as many as you want:

SELECT * FROM table ORDER BY category ASC, ID DESC LIMIT 9

~Brett

johanimmers

Thank you for your replies, I realy appreciate your help. But I see now that my question was not clear enought. My problem goes one step further, and i forgot to mention one crucial part.

I want to select the last x records per category. That "x" must be a constant. For example, I want (for every category, the last posted records.

category 1:
record 24
record 22
record 17

category 2:
record 25
record 23
record 19

category 3:
record 18
record 15
record 12

etc...

I'm sorry that my question was not complete. I'm working on this problem for some time now. I know there is a very simple way to do this with multiple queries. But this time I realy want to optimize my scripts, therefor it would realy be nice to do this one with one query. But I'm not sure if it is possible.

johanimmers

Wups, I can't edit my reply. Still my question is not clear enough 😉 the "x" needs to be a constant. Like x=3.

bpat1434

Umm.... that's what the LIMIT is for. Here's what I would do (although may not be the best).

Create a loop for as many categories as you have (by counting # of rows in Categories table, or setting number manually):

$query  = "SELECT * FROM `Categories` ORDER BY id ASC";
$result = mysql_query($query);
$max = mysql_num_rows($result);

for($i = 1; $<=$max; $i++){

Now, query the database for each of the last 3 records in the category.

$sql = "SELECT * FROM `Records` WHERE catID = '$i' ORDER BY id DESC LIMIT 3";
$sqlrslt = mysql_query($sql);
whie($row = mysql_fetch_array($sqlrslt)){
    // Do whatever you want with the information
}

That's what I would do, or some variation.

~Brett

johanimmers

Thanks again for your help. It is still not exactly what I want, but I think this will be the best (and only solution). This solution means that for every category a query must be made to select the last 3 records. Actually it is no problem for those extra queries, but I'm trying to use as few queries as possible in the current project I'm working on.

The page where this query will be used, is not that demanding, there are not that many categories (therefor not that many queries). But I know it is possible if I could use a subquery. (like in MySQL 4.x somewhere). Then I could select the last three records per categorie. Well, atleast I'm pretty sure that it will be possible with a subquery. But i'm not so sure if it is possible without.

So I can live with this solution 😉 and I thank you again for your willing to help.