MySQL Query: select the most recent elements with a swirl

Question

Sorry, the name is no more. I have a database of media file URLs that come from two sources:

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

I want to find the ten most recently added URLs, but not more than one from any feed. To simplify, table ' urls' has columns 'url, feed_id, timestamp'.

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

How do I write a request? Remember, I want the ten most recent URLs, but only one of all feed_id.

Answer 1

Assuming feed_id = 0 is manually entered material, 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;

, id , . t - .

:

(
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

Answer 2

  CREATE TABLE feed (
  feed varchar(20) NOT NULL,
  add_date datetime NOT NULL,
  info varchar(45) NOT NULL,
  PRIMARY KEY  (feed,add_date);

, . 10 , .

  select f2.*
  from (select feed, max(add_date) max_date
          from feed f1
         group by feed
         order by add_date desc
         limit 10) f1
  left join feed f2 on f1.feed=f2.feed and f1.max_date=f2.add_date;

Answer 3

() :

CREATE TABLE programs (
  id int(11) NOT NULL auto_increment,
  feed_id int(11) NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (id)
) ENGINE=InnoDB;

sambo99:

(SELECT feed_id,id,timestamp 
    FROM programs WHERE feed_id='' 
    ORDER BY timestamp DESC LIMIT 10)
UNION
    (SELECT feed_id,min(id),max(timestamp) 
    FROM programs WHERE feed_id<>'' GROUP BY feed_id 
    ORDER BY timestamp DESC LIMIT 10)
ORDER BY timestamp DESC LIMIT 10;

, . , , , . ( !). "id"?

Answer 4

, union. - :

    (SELECT 
        url, feed_id, timestamp 
    FROM rss_items  
    GROUP BY feed_id 
    ORDER BY timestamp DESC 
    LIMIT 10)
UNION
    (SELECT 
        url, feed_id, timestamp 
    FROM manual_items  
    GROUP BY feed_id 
    ORDER BY timestamp DESC 
    LIMIT 10)
ORDER BY timestamp DESC
LIMIT 10

Answer 5

MySQL .

, "GROUP-BY" "HAVING", , .

, , : http://www.artfulsoftware.com/infotree/queries.php?&bw=1390#104

( , , K , , . , LIMIT).

Answer 6

, ?

SELECT url, feedid FROM urls GROUP BY feedid ORDER BY timestamp DESC LIMIT 10;