How to Display Half Records from Table - MySQL

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



Leave a reply



Submit