Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)?

share|improve this question
    
Do you want the 50th percentile or half the rows? –  Phil Jul 2 '12 at 13:42
    
@Phil I want half the rows. Basically I want to select the first half and do something with in and then select the 2nd half and do something with it. Which is why i want it to be dynamic. I don't want to store the number of rows and then divide by 2 or anything like that. –  hanleyhansen Jul 2 '12 at 13:44
2  
In what order though? What defines "half"? –  Phil Jul 2 '12 at 13:44
    
@Phil I'll sort them by alphabetical order ascending. Half meaning if I have 80 rows sorted A-Z I want to be able to select the first 40. –  hanleyhansen Jul 2 '12 at 13:48
    

2 Answers 2

up vote 4 down vote accepted

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 !!!

share|improve this answer
    
Works like a charm! Thanks! –  hanleyhansen Jul 2 '12 at 16:24

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

share|improve this answer
    
Why are you answering this question? It's long since been answered to the asker's satisfaction, and so far as I can tell, this answer doesn't add any extra value beyond what was already provided. –  Jon Seigel Mar 25 '13 at 1:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.