I have a database and wish to display the top 5 no. of asked questions for Chart.js by using the question as label and no. of ask as value. May I know how can I do?

CodePudding user response：

To display the top 5 by number of asks, in mySQL you can write

SELECT `question`, `no_asks`
FROM `yourtablename`
ORDER BY `no_asks` DESC
LIMIT 5

The

ORDER BY `no_asks` DESC 

lists the results in descending order of the number of asks (so you'll get the most asks first)

and the

LIMIT 5

restricts the results to a maxmimum of 5 rows.

More info

• ORDER BY: https://www.mysqltutorial.org/mysql-order-by/
• LIMIT: https://www.mysqltutorial.org/mysql-limit.aspx

CodePudding user response：

Use dense_rank() function to display the top 5 number of asks in case of duplication.

Assume that your table name is chart_testSO

select question,no_ask from(
select dense_rank() 
OVER (  order by no_ask desc )  as r,* from chart_testSO
)temp
where temp.r<=5

• Dense rank: https://www.mysqltutorial.org/mysql-window-functions/mysql-dense_rank-function/

On different MySQL versions there is no dense_rank() function. You can use following query

set @val =1;
set @pk1:=(select no_ask from chart_testSO order by no_ask desc limit 1);

SELECT  question,
no_ask,
denseRank
FROM
(
SELECT  question,
no_ask,
@val := if(@pk1=no_ask,  @val, @val 1) as denseRank,
@pk1 := no_ask
FROM
(  SELECT  question, no_ask
FROM    chart_testSO
ORDER BY no_ask desc) A
) B where denseRank<=5;

• Alternate of Dense Rank MySQL: https://www.folkstalk.com/2013/03/grouped-dense-rank-function-mysql-sql-query.html
• If MySQL:https://www.w3schools.com/sql/func_mysql_if.asp