There is a table "News" with fields:

id_news int(11) name tinytext body text putdate datetime objavlenije enum('yes', 'no')

You need to do a sort by two fields with the output of unique related content from this table. at the beginning there should be a display of news by date in the amount of 10 pieces. with the value Yes in objavlenije , and then the output of all the remaining news by date, regardless of the value objavlenije .

How to implement it?

  • and with such request it is possible to think up something? SELECT * FROM system_news ORDER BY CASE objavlenije WHEN 'yes' THEN 1 WHEN 'no' THEN 2 END, putdate DESC - vp111

2 answers 2

As an alternative to JOIN , UNION can be used.
However, due to the fact that ORDER BY should be used only for the combined sample, in this case you will need to use a "crutch" with LIMIT to preserve the order of the elements from the subqueries:

 (SELECT * FROM News WHERE objavlenije = 'yes' ORDER BY putdate LIMIT 10) UNION (SELECT * FROM News ORDER BY putdate LIMIT 10000000)

Instead of 10000000 you need to substitute a value that will be obviously more than the number of records in the table.

An example in SQL Fiddle .

  • and with such request it is possible to think up something? SELECT * FROM system_news ORDER BY CASE objavlenije WHEN 'yes' THEN 1 WHEN 'no' THEN 2 END, putdate DESC - vp111
  • "SELECT * FROM system_news" is executed in the set of classes, and the addition to it "ORDER BY CASE objavlenije WHEN 'yes' THEN 1 WHEN 'no' THEN 2 END, putdate DESC" can be edited - vp111
  • @ vp111 But why invent something with him? If you didn’t need to display exactly 10 messages with yes forward, then you could limit SELECT * FROM system_news ORDER BY objavlenije, putdate DESC to just SELECT * FROM system_news ORDER BY objavlenije, putdate DESC . And with this condition, I do not have the slightest idea how to do it in one SELECT . - Regent
  • there is simply a framework from Softtime, and there is such a construction $ obj = new pager_mysql ($ tbl_news, "", "ORDER BY CASE objavlenije WHEN 'yes' THEN 1 WHEN 'no' THEN 2 END, putdate DESC", $ pnumber, $ page_link ); All this automatically creates a news table with navigation. - vp111
  • "it would be possible to limit it to just SELECT * FROM system_news ORDER BY objavlenije, putdate DESC" can do so - vp111

union re-sorts the sample, union all - duplicates records, unless you do something special. You can do this:

 select * from News left join ( Select id_news from News where objavlenije = 'yes' order by putdate LIMIT 10 ) top10 using(id_news) order by if(top10.id_news is null, 1, 0), putdate
  • In fact, with UNION you can do this ("something special"). A bit of a crutch will look, but still. - Regent
  • Of course it is possible. For example, using union all - you just need to exclude the result of the first sample, for example, not in subquery . through union - re-sort again. You can still look at exactly how the union sorts records to exclude duplicates and jump on it. It is possible through the user variable. And still somehow - there are a lot of ways even on not the most functional mysql. - Fine
  • Yes, no, the usual UNION (not UNION ALL ). If you use UNION ALL and then use a bunch of tricks / selektov to exclude duplicates, then this option, I think, will lose to your performance and appearance. And if you use the usual UNION , as the author of the remote answer did (and why did he delete it?), Then it should work with a small crutch. Whether I, perhaps, now give the same answer as the remote ... - Regent