SQL query to select at least 10 results

Question

There are two types of results that I want to return:

• Unread notifications
• Reading notifications

If there are> 10 unread notifications, I want to choose as many as there are

If there is <= 10, I want to select all (say 7) unread notifications and 3 'fill' readings. How can i do this?

If I just wanted to select all unread notifications, my request is:

SELECT * FROM notifications WHERE read = 0 

If I just wanted to select all the notifications I read, my request is:

 SELECT * FROM notifications WHERE read = 1 

Answer 1

This should help you: http://sqlfiddle.com/#!9/e7e2a/2

 SELECT * FROM ( SELECT @rownum := @rownum + 1 AS rownum, name, read FROM notifications, (SELECT @rownum := 0) r --initialise @rownum to 0 ) t WHERE read = 0 OR (read = 1 AND rownum <= 10) ORDER BY rownum 

Entries are numbered with @rownum. The where clause ensures that read = 0 will be selected first. If they are up to 10 or more, all are selected. But if not, the second criterion is checked (read = 1 AND rownum <= 10) .

(SELECT @rownum := 0) r initializes @rownum to 0, otherwise it will be NULL and NULL+1=NULL

Answer 2

What you can do is combine the two results, sort by the most important, and then limit the Union:

 SELECT Col1, Col2, ... FROM ( SELECT Col1, Col2, `read`, ... FROM notifications WHERE read = 0 UNION SELECT Col1, Col2, `read`, ... FROM notifications WHERE read = 1 ) x ORDER BY x.`read` -- And probably a column like Time? LIMIT 10; 

SqlFiddle here

Edit, Re: Must return ALL Unread, not just the first 10

Sorry, I skipped this part of the question. I can’t come up with an elegant way to achieve this, therefore here is a partial solution that resorts to an imperative procedure and a temporary table to fill in the rows if necessary: use codingbiz solution until MySql supports Windowing functions (e.g. ROW_NUMBER() OVER (PARTITION BY read ORDER BY Col1 DESC)

Answer 3

If the table does not reach a large one, you can try to join it as follows

 SELECT *, ROW_NUMBER() OVER ( ORDER BY read ) AS RowNum FROM ( SELECT * FROM notifications WHERE read = 0 UNION SELECT * FROM notifications WHERE read = 1 ) T1 WHERE T1.read = 0 OR (T1.read = 1 AND T1.RowNum <= 10) ORDER BY T1.read DESC 

When these tables become large, you can try to start the count in the “read” table first and see if it has more than 10 unread messages and based on this result choose read or unread messages

 INT @readMessages = SELECT COUNT(*) FROM notifications WHERE read = 0 SELECT CASE WHEN @readMessages > 10 THEN SELECT * FROM notifications WHERE read = 0 ELSE ( SELECT * FROM notifications WHERE read = 0 UNION SELECT * FROM notifications WHERE read = 1 LIMIT 0, 10-@readMessages ) 

I don't know if this is all correct MySQL syntax (more SQL guy), but maybe this helps you.

Answer 4

Please check this solution.

 SELECT * FROM `notifications` WHERE `read`=1 OR `read`=0 ORDER BY `read` LIMIT 10 

http://sqlfiddle.com/#!9/c72af/1