SQL query to select minimum of 10 results

Question

There are two types of results I want to return:

• Unread notifications
• Read notifications

If there are > 10 unread notifications available I want to select as many as there are

If there are <= 10, I want to select all (say there were 7) the unread notifications and 3 'filler' read notifications. How can I accomplish this?

If I wanted to just select all unread notifications my query would be:

SELECT * FROM notifications WHERE read = 0

If I wanted to just select all read notifications my query would be:

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

The records are numbered with @rownum. The where clause make sure the read=0 are selected first. If they are up to 10 or more, all are selected. But if not, the second criteria (read = 1 AND rownum <= 10) is checked.

(SELECT @rownum := 0) r initialises @rownum to 0 otherwise it would be NULL and NULL+1=NULL

Answer 2

What you can do is union the two results, order by 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 first 10

Apologies, I missed that part of the question. I can't think of an elegant way to achieve this, so here's a ~partial solution which resorts to an imperative procedure and a temporary table to fill up the rows, if needed: use codingbiz's solution until MySql supports Windowing functions (e.g. ROW_NUMBER() OVER (PARTITION BY read ORDER BY Col1 DESC)