maxhud maxhud - 25 days ago 6
MySQL Question

SQL query to select minimum of 10 results

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

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

enter image description here