DIviding the SQL result into two halves

Question

The SQL query is :

Select ProductName from Products;

The above query returns 5000 rows.

How can the result of 5000 rows be divided into two result sets of 2500 rows each,.i.e., one result set from 1 to 2500 and the other from 2501 to 5000?

Note:

Here ProductName is the primary Key.No ProductID column is present in the table.
It can be done either in the back end or in the front end.

Read the docs for your variant of SQL. `LIMIT`, `OFFSET ... FETCH` etc. — O. Jones, Nov 06 '20 at 10:47
Seriously consider handling issues of data display in application code — Strawberry, Nov 06 '20 at 11:04

buddemat · Answer 1 · 2020-11-06T11:11:11.713

An approach that works for mySQL (based on this answer https://stackoverflow.com/a/4741301/14015737):

Upper half

  SELECT *
    FROM (
             SELECT test.*, @counter := @counter +1 counter
               FROM (select @counter:=0) initvar, test
           ORDER BY num
         ) X
   WHERE counter <= round(50/100 * @counter);
ORDER BY num;

Lower half

Invert the sort order and remove the rounding

  SELECT *
    FROM (
             SELECT test.*, @counter := @counter +1 counter
               FROM (select @counter:=0) initvar, test
           ORDER BY num DESC
         ) X
   WHERE counter <= (50/100 * @counter);
ORDER BY num;

In case of an uneven number of records, the middle record is added to the upper half in this example. If you want it the other way around, move the round() to the other statement. If you don't want it at all, remove round().

Dbfiddle example: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=fb70eae0f7f1434a24099b5bb19f0878

Thorsten Kettner · Answer 2 · 2020-11-06T12:26:43.783

The easiest and probably fastest approach is to use the table's primary key if you are fine with getting the rows in its order.

Run

select productname, id from products order by id;

and fetch 2500 rows. Then with the last ID, say ID 3456, run

select productname, id from products where id > 3456 order by id;

and fetch 2500 rows again. Etc.

UPDATE: Seeing I got a downvote for this, I'll better explain :-)

The query returns 5000 rows now and the OP doesn't want that many rows, so they want to cut this in halves. But the query may well return 10000 rows next year. Will the OP suddenly be fine with getting 5000 rows at once? This doesn't seem likely. It is more likely that there is an amount of rows that shall not be surpassed. This is why I cut the amount into slices of 2500.

The other approach to number all rows and return the first n rows has a severe drawback: All rows must be read again. Even if it is decided to cut the result in chunks of 100 each, everytime all rows must be read, sorted, numbered, fetched from. Reading all rows from a table and sorting all these rows is a lot of work for a DBMS.

I'm not sure why this would be downvoted. It seems like a reasonable approach, although the example of 1234 doesn't make that much sense if 2500 rows are fetched, given that numeric primary keys are usually not negative. — Gordon Linoff, Nov 06 '20 at 12:05
@Gordon Linoff: Thank you. Yes, the ID 1234 didn't make sense. I've corrected this. — Thorsten Kettner, Nov 06 '20 at 12:10

score 0 · Answer 3 · answered Nov 06 '20 at 12:03

If you know the numbers that you want, just use limit:

select ProductName
from Products
order by id

And then either:

limit 2500
limit 2500 offset 2499

If you simply want the results split into half, then you can use:

select t.*
from (select t.*,
             ntile(2) over (order by <primary key>) as tile
      from t
     ) t
where tile = 1;  -- or 2 for the other half

DIviding the SQL result into two halves

3 Answers3