Mysql get top half of table then bottom half of table
I would suggest doing 2 query's:
select count(*) from t_domains
to get the total count, and then get the data you want by using limit and offset:
select * from t_domains limit count/2
for the top and
select * from t_domains offset count/2
for the lower half......
This approach gives you also a way to limit the query's in another situation: what if the table contains 1 million records?
How to split MySQL table records in half to display on each side of a page
You can just round it down so you always get an integer. That way, a decimal won't break the query.
$half = floor($numRows/2);
You'll want to use a new $half
value using ceil()
on the other side to round up, so you don't skip a row in the middle.
As an aside, it might be better to do just one query returning every row, then output the rows up to $half
on one side, then continue from that point to the end for the other side. Then you're only hitting the database once and it's easier to see what's going on.
e.g
$result = mysql_query("SELECT * FROM cultures ORDER BY name ASC");
$half = floor(mysql_num_rows($result)/2);
$count = 0;
// First side.
while($count <= $half
&& $row = mysql_fetch_array($result))
{
// ...
$count++;
}
// ...
// Second side.
while($row = mysql_fetch_array($result))
{
// ...
}
How to retrieve half of records from a table - Oracle 11g
Well, you could count the rows and select half:
select *
from my_table
where rownum <= (select count(*)/2 from my_table)
That would tend to select rows that are contiguous within the physical segments.
Or ...
select *
from (select rownum rn, * from my_table)
where mod(rn,2) = 0
That would tend to select "every other" row, so you'd get a pretty even spread from the physical data segments.
Or ...
select *
from my_table sample (50)
That would be approximately half of the rows.
Or ...
select *
from my_table sample block (50)
That would be the rows from approximately half of the data blocks below the high water marks of the segments.
Probably lots of different ways available, and which one you want probably depends on whether you want the selected pseudo-randomly or not.
If you want to use the output of the query, use something like:
select ...
from (select *
from my_table
where rownum <= (select count(*)/2 from my_table)) my_table
join ...
In that circumstance the SAMPLE syntax would be more compact.
Select only half the records
In oracle you can use the ROWNUM psuedocolumn. I believe in sql server you can use TOP.
Example:
select TOP 50 PERCENT * from table
Related Topics
Sqlcmd Not Able to Find a Library (Libmsodbcsql-17.0.So.1.1) That Is There
Presto SQL - Converting a Date String to Date Format
How to Delete a MySQL Record After a Certain Time
Mismatched Input 'From' Expecting <Eof> SQL
Posgresql - Error: Relation "Table_Name" Does Not Exist. How to Query Without Schema Name
How to Limit Results of a Left Join
Select Only Rows That Contain Only Alphanumeric Characters in MySQL
How to Select All Records from One Table That Do Not Exist in Another Table
Insert If Not Exists Else Update
Sql - Return a Default Value When My Search Returns No Results Along With Search Criteria
How to Find Specific Values in a Table in Oracle
How to Add a Space Between Two Text in SQL Code
Sql Server How to Return Null Instead of 0 If a Grouped Value Doesn't Have Rows in Source
Make SQL Select Same Row Multiple Times
How to Check If a SQL Server String Is Null or Empty