mysql check if a value is in a range or not

I have a mysql (mariadb) table of pictures with the id and score of them, like this:

TABLE Pics
-----------
id   points
-----------
1    500
2    10
3    150
4    70
...

The table has more columns and can have a lot of registers.

I want to know how to know if a picture could be tagged as "popular". For the system, "popular" would be the 1% of pictures with more points. So, if I have 1.000 pictures in the table, just the 10 pictures with more points would be tagged as "popular".

So, how could be the query to know if the picture with a given id is popular or not?

Many thanks!

Solution

You can either use a LIMIT clause after sorting them by score to only select the 10 most popular photos or if you need to select all the photos, you can use a variable like the following query :

SET @rank = 0;
SELECT IF((@rank := @rank + 1) <= 10, "popular", "unpopular"), p.*
    FROM picture_table AS p
    ORDER BY p.score DESC;

if you need the limit to be dynamic, you can do a first query to evaluate the total number of elements and use it in that query like so :

SET @rank = 0;
SET @limit = 10/100; -- 10%

SELECT (@limit := ROUND(COUNT(ID) * @limit)) FROM picture_table;

SELECT IF((@rank := @rank + 1) <= @limit, "popular", "unpopular"), p.*
    FROM picture_table AS p
    ORDER BY p.score DESC;

Now with that query, you can use a subquery to determine if a particular picture is popular :

SET @rank = 0;
SET @limit = 10/100;

SELECT (@limit := ROUND(COUNT(ID) * @limit)) FROM picture_table;

SELECT Q1.popularity, p.* 
    FROM picture_table AS p
    INNER JOIN (SELECT IF((@rank := @rank + 1) <= @limit, "popular", "unpopular") AS 'popularity', p.ID
        FROM picture_table AS p
        ORDER BY p.score DESC) AS Q1 ON Q1.ID = p.ID
    WHERE p.ID = 16;

Keep in mind it is better to limit the amount of data generated by the sub query to the bare minimum so that the query keeps a good performance when the database gets bigger.

Here's a DB-Fiddle with all those scripts with working sample data