SELECT TOP in MySQL

Question

How can I do this in MySQL?

SELECT TOP 50 PERCENT * FROM table

What's the simplest way to do it without running to separate queries (if possible)?

Answer 1

There is no TOP n PERCENT syntax in MySQL.

You will have to emulate it as follows

First here is a sample table

mysql> use test
Database changed
mysql> drop table if exists mytable;
Query OK, 0 rows affected (0.06 sec)

mysql> create table mytable (id int not null auto_increment primary key);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into mytable values (),(),(),(),(),(),(),(),(),();
Query OK, 10 rows affected (0.07 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from mytable;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+
10 rows in set (0.01 sec)

mysql>

You emulate it with this code:

set @percent = 50;
select floor(count(1) * @percent / 100.0) into @pct from mytable;
set @sqlstmt = concat('select * from mytable limit ',@pct);
prepare stmt from @sqlstmt;
execute stmt;
deallocate prepare stmt;

Here is that code executed:

mysql> set @percent = 50;
Query OK, 0 rows affected (0.00 sec)

mysql> select floor(count(1) * @percent / 100.0) into @pct from mytable;
Query OK, 1 row affected (0.00 sec)

mysql> set @sqlstmt = concat('select * from mytable limit ',@pct);
Query OK, 0 rows affected (0.00 sec)

mysql> prepare stmt from @sqlstmt;
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> execute stmt;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

mysql> deallocate prepare stmt;
Query OK, 0 rows affected (0.00 sec)

mysql>

Give it a Try !!!

Answer 2

I am sharing logic here

Step 1 :: find total no of rows in table

var a = select count(*) in table_name

Step 2 :: find 50% value of var a

var a = (var a / 2)

step 3 :: mysql query with row limits

select * from table_name limit