Mysql – How to get the top 20 percent records with a single query

mariadbMySQLoffset-fetch

SELECT start_coins
FROM   coins
ORDER BY  start_coins DESC LIMIT 10; /* till here the query works*/
CAST((COUNT(start_coins) * 0.2) AS INT);

I want the number returning from the count to be used as the LIMIT instead of 10. My database version is 10.1.32-MariaDB.

Best Answer

The LIMIT clause parameters are always literals. So You cannot use the value calculated by some statement in it.

The solution can be in prepared statement use.

Variant 1:

SET @sql := CONCAT( 'SELECT start_coins FROM coins ORDER BY start_coins DESC LIMIT '
                  , (SELECT ROUND(COUNT(*) * 0.2) FROM coins));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;

Variant 2:

SELECT COUNT(*) * 0.2 INTO @cnt FROM coins;
PREPARE stmt FROM 'SELECT start_coins FROM coins ORDER BY start_coins DESC LIMIT ?;';
EXECUTE stmt USING @cnt;
DROP PREPARE stmt;

In both variants You MUST to execute all 4 statements in the same connection to database. The best vairant is the connector You use allows multi-statements (sending all statements as 1 command).

Related Solutions

Mysql – How to write a query that counts rows by date

That SQL dialect is from Oracle but this answer will be for MySQL.

If datum is DATE

select count (*),datum date from tx_feeder 
where datum between (CURDATE() - INTERVAL 31 DAY)
and (CURDATE() - INTERVAL 1 DAY)
group by datum;

If datum is DATETIME or TIMESTAMP

select count (*),DATE(datum) date from tx_feeder 
where datum between (CURDATE() - INTERVAL 31 DAY)
and (CURDATE() - INTERVAL 1 DAY)
group by DATE(datum);

If you are looking for a grand total to be included, do WITH ROLLUP

select count (*),datum date from tx_feeder 
where datum between (CURDATE() - INTERVAL 31 DAY)
and (CURDATE() - INTERVAL 1 DAY)
group by datum WITH ROLLUP;

select count (*),DATE(datum) date from tx_feeder 
where datum between (CURDATE() - INTERVAL 31 DAY)
and (CURDATE() - INTERVAL 1 DAY)
group by DATE(datum) WITH ROLLUP;

Mysql – Is a “Ranking” query’s ORDER BY in FROM subquery is preserved

As the article explains: No, the internal ORDER BY will be recognized by MariaDB's optimizer as redundant and will be "optimized away". If you want a result set returned in a specific order, use ORDER BY - in the most external level of the query.

The solution is to use ORDER BY in the external query. The x subquery (derived table) can be removed and replaced by a simple sakila.address x (that's what the optimizer will recognize and do anyway):

SELECT CASE 
         WHEN   @prev = x.city_id
            THEN @a
         WHEN @prev := x.city_id
            THEN @a := @a + 1
       END
         `rank`     
FROM 
    (SELECT @prev := NULL, @a := 0) y,
    sakila.address x                    -- remove the derived table

ORDER BY x.city_id ;                    -- add ORDER BY in the external query

About the second query, yes, I think the ORDER BY x.city_id will be ignored, too. The answer in the linked question is 6 years old and is accurate but it's for MySQL (and the versions available then). MariaDB recent improvements on the optimizer make this kind of subqueries with variables, sometimes to fail because the ORDER BY is "optimized away".

Best Answer

Related Solutions

Mysql – How to write a query that counts rows by date

Mysql – Is a “Ranking” query’s ORDER BY in FROM subquery is preserved

Related Question