MySQL Query: Select most-recent items with a twist

Question

Sorry the title isn't more help. I have a database of media-file URLs that came from two sources:

(1) RSS feeds and (2) manual entries.

I want to find the ten most-recently added URLs, but a maximum of one from any feed. To simplify, table 'urls' has columns 'url, feed_id, timestamp'.

feed_id='' for any URL that was entered manually.

How would I write the query? Remember, I want the ten most-recent urls, but only one from any single feed_id.

Answer 1

Assuming feed_id = 0 is the manually entered stuff this does the trick:

select p.* from programs p
left join 
(
    select max(id) id1 from programs
    where feed_id <> 0
    group by feed_id
    order by max(id) desc
    limit 10
) t on id1 = id
where id1 is not null or feed_id = 0 
order by id desc
limit 10;

It works cause the id column is constantly increasing, its also pretty speedy. t is a table alias.

This was my original answer:

(
select 
    feed_id, url, dt 
    from feeds  
    where feed_id = ''
    order by dt desc 
    limit 10
)
union
(

select feed_id, min(url), max(dt) 
        from feeds
        where feed_id <> '' 
        group by feed_id
        order by dt desc    
        limit 10
)
order by dt desc
limit 10