CODEWITHSUNDEEP
mysqlsql
Max Hudson
Max Hudson

Reputation: 10206

SQL query to select minimum of 10 results

There are two types of results I want to return:

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

Upvotes: 6

Views: 1061

Answers (4)

Aravind Kumar Anugula
Aravind Kumar Anugula

Reputation: 1326

Please check this solution. 

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

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

Upvotes: -1

StuartLC
StuartLC

Reputation: 107237

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)

Upvotes: 5

codingbiz
codingbiz

Reputation: 26376

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

Upvotes: 5

Rik
Rik

Reputation: 3887

If the table does not get to big you could try joining them 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 do get big, you could try to run a count on the 'read' table first and see if it has more than 10 unread messages, and based on that result select 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
    )

Don't know if it is all proper MySQL syntax (more an SQL guy), but maybe it helps you.

Upvotes: 0

Related Questions