MySQL: How to SELECT only three of each differing x-column-name

Question

I want to display my blogs in a very future proof way. So I need to make a query that will reference new blog types if they are created. Hence the x-column-name. In this case that's blogType. This select query should contain the blog information for all blog types, but for each blog type get 3 Blogs. It's kind of confusing so this is why I am reiterating!

I have done a bit of work already in googling how to limit the results. Shown below:

 $query = 'SELECT * FROM blogs
              ORDER BY dateWritten
              ASC LIMIT 3';

I'll be outputting the results to blog_rss.php using an array and a foreach loop. I get the array from a function like this:

function get_Recent_Blogs() {
    global $db;
    $query = 'SELECT * FROM blogs
              ORDER BY dateWritten
              ASC LIMIT 3';
    try {
        $statement = $db->prepare($query);
        $statement->execute();
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        $statement->closeCursor();
        return $result;
    } catch (PDOException $e) {
        $error_message = $e->getMessage();
        display_db_error($error_message);
    }
}

It's not too important but at least it gives you some context.

    SELECT *
    FROM (
         SELECT b.*, ROW_NUMBER() OVER (PARTITION BY b.blogType ORDER BY b.blogID DESC) as rn
         FROM blogs b
         ) x
    WHERE x.rn <= 3
    ORDER BY x.blogType, x.blogID DESC';

So I've done my best to implement the solution but I'm getting some errors. I'm not sure if I should start a new post for this one or not but the code above is what I used and this is the error I'm getting:

You have an error in your SQL syntax; it seems the error is around: '( PARTITION BY b.blogType ORDER BY b.dateWritten DESC ' at line 7

So let's clarify: You need a query which returns three most recent articles per blog type. Is that correct? — Paul Spiegel, May 11 '19 at 14:33

Paul Spiegel · Accepted Answer · 2019-05-13T13:05:43.830

Since your MySQL version supports window functions, you can use ROW_NUMBER(), which will enumerate your entries per blog type. Then you just need to pick the first three rows per type.

SELECT *
FROM (
    SELECT b.*, ROW_NUMBER() OVER (PARTITION BY b.type ORDER BY b.dateWritten DESC) as rn
    FROM blogs b
) x
WHERE x.rn <= 3
ORDER BY x.type, x.dateWritten DESC -- adjust as needed

Notes:

I assume that the blog type is determined by the type column. Adjust it if needed.
You should use DESC instead of ASC, since you want to get the most recent entries.
I would rather use an AUTO_INCEMENT id column instead of dateWritten for sorting. That is more reliable, since a DATE or even a TIMESTAMP can have duplicates.

For older versions which don't support window functions I would first fetch all types and generate a UNION ALL query. With PDO it could be something like the following:

$types = $db
       ->query('SELECT DISTINCT type from blogs ORDER BY type')
       ->fetchAll(PDO::FETCH_COLUMN);

$subqueries = array_map(function($type){
    return '(SELECT * FROM blogs WHERE type = ? ORDER by dateWritten DESC LIMIT 3)';
}, $types);

$query = implode(' UNION ALL ', $subqueries) . ' ORDER BY type, dateWritten DESC';

$statement = $db->prepare($query);
$statement->execute($types);
$result = $statement->fetchAll(PDO::FETCH_ASSOC);

This will generate the following query:

(SELECT * FROM blogs WHERE type = ? ORDER by dateWritten DESC LIMIT 3)
UNION ALL
...
UNION ALL
(SELECT * FROM blogs WHERE type = ? ORDER by dateWritten DESC LIMIT 3)
ORDER BY type, dateWritten DESC

Note 1: Even though two queries are executed, given a composite index on (type, dateWritten) this can still be faster than other solutions. When you have a couple of blog types and many articles per type, this can even be faster than the ROW_NUMBER() solution.

Note 2: I would usually have a separate table for types, and the blogs table would reference the primary key type_id column. In that case the first query would be SELECT type_id from blog_types, and the subqueries would have the condition WHERE type_id = ?.

Thank you! I am very newbish in MySQL so this is very helpful. I appreciate it! — Daniel J. Dunevant, May 11 '19 at 14:55
Wouldn't happen to know what's wrong with the syntax here would you? — Daniel J. Dunevant, May 12 '19 at 23:46
@DanielJ.Dunevant Do you get an error? It works for me here: https://www.db-fiddle.com/f/s3qAyCDo7ScQorPg98sCMt/0 — Paul Spiegel, May 13 '19 at 12:18
I did... :( In testing the code that is valid in 5.7 I got the error of: "SQLSTATE[HY093]: Invalid parameter number: no parameters were bound." The fiddle code is for 8.0 though so it doesn't work for me currently. — Daniel J. Dunevant, May 14 '19 at 11:04
@DanielJ.Dunevant You wrote your version is 8.0.13, but the error in question with "near ( PARTITION" tells me that it's not. However - That's why I wrote a second solution. "no parameters were bound" - Seems that you've changed something. Be sure you pass the types here: `$statement->execute($types);` — Paul Spiegel, May 14 '19 at 11:22
I'm very new. I did realize that later on I was on 5.7 but neglected to mention it! Also you are again right! I didn't put the types there! I really need to look more closely! It now functions perfectly! Thank you so much for helping me through my ignorance. Now I'm hopefully slightly less so! — Daniel J. Dunevant, May 14 '19 at 11:53

MySQL: How to SELECT only three of each differing x-column-name

1 Answers1