how to Display half records from table? MYSQL

Question

I am looking for MYSQL QUERY, not PLSQL QUERY

select *  from aadhar limit (select count(*)/2 from aadhar);

I tried to like this but getting this error please help

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select count(*)/2 from aadhar )' at line 1

Answer 1

Like the other answers already explained LIMIT can't be dynamically set in a query.
But you can workaround it with a MySQL user variable in combination with PREPARE/EXECUTE..

SET @count = (SELECT COUNT(*)/2 FROM aadhar); 
SET @sql = CONCAT('SELECT * FROM aadhar LIMIT ', @count);

PREPARE q FROM @sql;
EXECUTE q;
DEALLOCATE PREPARE q;

Or (safest option)

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

PREPARE q FROM 'SELECT * FROM aadhar LIMIT ?';
EXECUTE q USING @count;
DEALLOCATE PREPARE q;

Extra warning

Please note that SQL is by definition orderless. Using LIMIT without ORDER BY is meaningless. SQL is free to return the records what matches in anny order it wishes without ORDER BY meaning running the same query twice might result in a different result.

Also it's better to use CEIL() or CAST() which converts a double into a int so the methodes will not error when the table has a odd number off records and generates SQL with a double like this SELECT * FROM aadhar LIMIT 1.500000000

see demo on how to do those