'MySql Query to find out first 50% of records from a Table

I am trying to fetch first 50% of records from a MySQL Table User. I know we can use limit or top for finding them but the total number of records are not fixed so hard coding the actual number in the limit or top doesn't gives me first 50% of records. How can I achieve this?

mysqlsqlsql-order-bywindow-functions


Solution 1:[1]

If you are running MySQL 8.0, you can use window functions for this: ntile() does exactly what you ask for. Assuming that your ordering column is id:

select *
from (select t.*, ntile(2) over(order by id) nt from mytable) t
where nt = 1

In earlier versions, one option is a user variable and a join with an aggregate query:

select *
from (
    select t.*, @rn := @rn + 1 rn
    fom (select * from mytable order by id) t
    cross join (select @rn := 0) x
    cross join (select count(*) cnt from mytable) c
) t
where rn <= cnt / 2

Solution 2:[2]

Mysql directly not supports this. You can try with two queries or use subqueries

Something like this.

  1. find the count of total records/2

  2. that value has to be applied in the limit clause.

    SET @count = (SELECT COUNT(*)/2 FROM table);

    SET @sql = CONCAT('SELECT * FROM table LIMIT ', @count);

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 GMB
Solution 2 jithu thomas

Related Questions