Select max 5 integers from set of 20

Question

I'm trying to select the 5 most viewed articles out of a list of the 20 most recent entries in a table. My table structure is essentially this:

id | date | title | content | views

My first thought was just to use an inner select to get the 20 most recent articles, then select from that, but I have yet to have any luck.

//doesn't work (my version of mysql doesn't support LIMIT in sub queries)
$recent = "(SELECT id FROM news ORDER BY date DESC LIMIT 20)";
$result = $db->query("SELECT id, title, date, content FROM news WHERE id IN $recent ORDER BY views DESC LIMIT ".self::RECENT_MAX);

//neither does this (syntax error @ 'OFFSET 20')
$recent = "(SELECT MAX(date) FROM news ORDER BY date DESC OFFSET 20)";
$result = $db->query("SELECT id, title, date, content FROM news WHERE date > $recent ORDER BY views DESC LIMIT ".self::RECENT_MAX);

Anyone got any suggestions on how you would structure this query?

Answer 1

Why don't you run the first (inner) query separately, and create the second query programmatically?

$ids = array();
$result = $db->query("SELECT id FROM news ORDER BY date DESC LIMIT 20");
while ($row = $result->fetch()) {
    $ids[] = $row[0];
}
$ids = implode(',', $ids);
$result = $db->query("SELECT id, title, date, content FROM news WHERE id IN ($ids) ORDER BY views DESC LIMIT ".self::RECENT_MAX);

Or something of this nature...

Update: Or you could just simply fetch the first 20 sorted by date, then sort thr result array by views in PHP, and finally take the 5 topmost items (I guess this is what SilentGhost meant in the comment).

Answer 2

If you're having a lot of issues getting it to work through SQL, I'd suggest just grabbing the 20 most recent articles from the database, then process it in PHP to find the 5 most-viewed. You could either loop over the rows, or just load it all into an array and sort it.

Answer 3

Since I can't think of any way to do that in a single query, I suggest either selecting the top 5 in code, or doing it using two queries, something like:

$items = $db->query("SELECT id FROM news ORDER BY date DESC LIMIT 20");
$recent = array();
foreach ($items->fetchAll() as $item) { $recent[] = $item['id']; }
$recent = "('".join("','", $recent)."')";

$result = $db->query("SELECT id, title, date, content FROM news WHERE id IN {$recent} ORDER BY views DESC LIMIT ".self::RECENT_MAX);

Answer 4

Try this...

select top 5 number from
(
    select 
      top 20 (ID) as number
    from 
     news order by date desc
)
as number

EDIT FOR MS SQL

Use the LIMIT for MYSQL

Answer 5

Does it support RowCount=5 ???

Answer 6

I just tested this and it works

SELECT *
FROM (
    SELECT *
    FROM news
    ORDER BY id DESC
    LIMIT 0, 20
) lasttwenty
ORDER BY views DESC
LIMIT 0, 5

Server version: 5.0.51a-3ubuntu5.4