SQL limit result to top 50%

Question

I tried two methods but failed in mysql.

/*see top 50% students, but this sql can't work*/
select * from student_table order by chinese_score desc limit count(*) * 0.5 ;

/*also can't work*/
set @num= floor((select count(*) from test.student_score)*0.5);
select * from student_table order by chinese_score desc limit @num ;

How to solve in mysql?

Possible duplicate of [link]http://stackoverflow.com/questions/8195430/variable-in-mysql-limit — etsa, Apr 05 '17 at 08:13
@etsa , Actually I run sqls in Hive, which doesn't support `PROCEDURE`. — yichudu, Apr 05 '17 at 08:21
@DuduMarkovitz , I also wonder the answer without PROCEDURE on mysql occasion. ;-) — yichudu, Apr 05 '17 at 09:03
@DuduMarkovitz , I saw your sqlserver edition post. I trust it's okay and u needn't have deleted it. — yichudu, Apr 05 '17 at 09:13
It's Hive (although it works for every DB that supports windows functions). As I said, the questions should not be mixed. I've removed the Hive tag. — David דודו Markovitz, Apr 05 '17 at 09:51

dsharew · Accepted Answer · 2017-04-05T09:52:26.410

In Mysql this can be done in a single query using user defined variables.

You can store a value in a user-defined variable in one statement and refer to it later in another statement. This enables you to pass values from one statement to another.

SELECT * FROM    (
          SELECT student_table.*, @counter := @counter +1 AS counter
          FROM (SELECT @counter:=0) AS initvar, student_table
          ORDER BY student_table.chinese_score DESC
        ) AS result
WHERE counter < (@counter/2) ORDER BY chinese_score DESC;

SQL limit result to top 50%

1 Answers1