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

StuartLC · Answer 1 · 2015-05-20T07:22:50.477

5

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)

edited May 20 '15 at 07:22

answered May 20 '15 at 06:21

StuartLC

104,537
17
209
285

4

Won't this only return 10 results if there are 15 unread notifications? – Max Hudson May 20 '15 at 06:27
Yes, this isn't quite right. If it was, it could be written much more simply. – Strawberry May 20 '15 at 06:30
Yes, good points both - [Strawberry](http://sqlfiddle.com/#!9/494df/4) and still working on the ALL or Max10 problem. I can't see a non-imperative solution however :( – StuartLC May 20 '15 at 06:33
1

@StuartLC What about something like 'LIMIT Max(10, Count(unread notifications))'? Another option is to select all unread and 10 read and just not show the extra read, but that's fairly inefficient because the user has to download potentially 50% more. – Max Hudson May 20 '15 at 06:37
1

Thanks for taking the time to look into it. Codebiz's solution worked for me. – Max Hudson May 20 '15 at 07:27
Yes - he's simulated the Windowing functions nicely by using a variable. I'm sure MySql will gain `PARTITION BY` at some point in future. – StuartLC May 20 '15 at 07:29

codingbiz · Accepted Answer · 2015-05-20T07:16:49.073

5

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

edited May 20 '15 at 07:16

answered May 20 '15 at 06:41

codingbiz

26,179
8
59
96

Can you explain what's going on here a little? Particularly r <= 10 and (SELECT @rownum := 0) r – Max Hudson May 20 '15 at 06:45
All rows are numbered, starting from the unread to the read. If unread >= 10, then all unread are selected. If unread < 10, the numbering continue into the read rows, but we only want the rows numbered <=10 – codingbiz May 20 '15 at 06:56
Just out of curiosity do you know the postgres equivalent of :=? What's that operator called? – Max Hudson May 20 '15 at 06:59
2

Use `row_number()`. Done differently in SQL Server and Postgresql http://stackoverflow.com/questions/3959692/rownum-in-postgresql – codingbiz May 20 '15 at 07:03
Technically this does answer my question, so I'm going to leave it as the accepted answer, but this doesn't necessarily work when you order by anything but rownum. It just so happens to luckily work with a date being sorted in descending order. – Max Hudson May 20 '15 at 07:31

score 0 · Answer 3 · answered May 20 '15 at 06:28

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.

found this: http://blog.sqlauthority.com/2014/03/08/mysql-generating-row-number-for-each-row-using-variable/ SET \@row_number:=0; SELECT *, \@row_number:=\@row_number+1 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 Sorry for the \@, but else it thinks I am referencing a user — Rik, May 20 '15 at 06:44

score -1 · Answer 4 · edited Nov 14 '16 at 16:52

-1

Please check this solution.

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

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

edited Nov 14 '16 at 16:52

mrsrinivas

34,112
13
125
125

answered May 20 '15 at 07:05

Aravind Kumar Anugula

1,304
3
14
35

SQL query to select minimum of 10 results

4 Answers4