Postgresql – Select consecutive number of rows starting from some number

postgresqlselect

What I need is to select 100 consecutive rows from a table, starting at some defined point without ordering data.

As far as I can observe, inserted data to a table is stored in no order but is retrieved by identical SELECT statements in the same order.

I could get use of 100 random rows, but random rows are not useful because I always need to retrieve the same data from the database in order to compare results of the algorithm I'm developing.

I don't want to order data by any field because otherwise I'd be biasing the selected data.

The sql statement should be something like:

SELECT field1, field2 FROM table1 WHERE row_number > 70000 LIMIT 100;

or probably even better (as I think LIMIT doesn't prevent the database from outputting every record above row number 70000):

SELECT field1, field2 FROM table1 WHERE row_number > 70000 AND row_number <= 70100;

What SELECT statement should I use?

Best Answer

You can try something like

SELECT field1, field2 
FROM table1 
ORDER BY ctid
OFFSET x
LIMIT 100
;

As I commented, without ordering you can never be sure that you get the same 100 rows. This way you will get the same rows in a certain order if they don't get updated or deleted.

Related Solutions

Mysql – How to re-arrange rows to best fit within groups

Here it the code from @DTest's Answer

SET @folder = 1;
SET @items = 0;
SELECT
    id, num_items,
    (SELECT IF(((@items:=@items+num_items)>100), @folder:=@folder+1, @folder)) as folder,
    IF(@items>100,@items:=0,@items) as checkItems
FROM foo;

Staring at this query, I would shudder to even think about writing of a Stored Procedure Technique for fitting items in folders. Then, it hit me ...

You could simply take the same query and ORDER BY num_items. That would actually pack as many items into a folder as possible before switching to another folder.

mysql> use ali
Database changed
mysql> CREATE TABLE foo (
    ->   id tinyint unsigned primary key AUTO_INCREMENT,
    ->   num_items tinyint unsigned
    -> );
Query OK, 0 rows affected (0.24 sec)

mysql>
mysql> INSERT INTO foo VALUES (1,4),(2,33),(3,74),(4,44),(5,24),(6,34),(7,46),(8,55),(9,11);
Query OK, 9 rows affected (0.17 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> SET @folder = 1;
Query OK, 0 rows affected (0.03 sec)

mysql> SET @items = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id, num_items, (SELECT IF(((@items:=@items+num_items)>100), 
    -> @folder:=@folder+1, @folder)) as folder,
    -> IF(@items>100,@items:=0,@items) as checkItems
    -> FROM foo ORDER BY num_items;
+----+-----------+--------+------------+
| id | num_items | folder | checkItems |
+----+-----------+--------+------------+
|  1 |         4 |      1 |          4 |
|  9 |        11 |      1 |         15 |
|  5 |        24 |      1 |         39 |
|  2 |        33 |      1 |         72 |
|  6 |        34 |      2 |          0 |
|  4 |        44 |      2 |         44 |
|  7 |        46 |      2 |         90 |
|  8 |        55 |      3 |          0 |
|  3 |        74 |      3 |         74 |
+----+-----------+--------+------------+
9 rows in set (0.08 sec)

mysql>

This packing would be somewhat lopsided in iterms of the number of entries per folder but the sum of the items per folder are pressed as close to 100 as possible.

Please note that if you want later folder with more items, simply change ORDER BY num_items to ORDER BY num_items DESC.

Give it a Try !!!

UPDATE 2013-01-09 12:52 EDT

What you need are three(3) mechanisms:

MECHANISM #1 : You need to imbed the query in a subquery
MECHANISM #2 : Order the subquery output by folder,num_items
MECHANISM #3 : Use a variable to monitor when you change folders from each row. When such a change occurs, you simply reset the folder counter to 1.

Here is the new code:

SET @order_val = 0;
SET @folder = 1;
SET @curr_folder = 1;
SET @items = 0;
SELECT *,
    @order_val:=IF(folder=@curr_folder,@order_val+1,1) ordervalue,
    @curr_folder:=folder current_folder
FROM
(
    SELECT * FROM
    (
        SELECT
            id, num_items,
            (SELECT IF(((@items:=@items+num_items)>100),
            @folder:=@folder+1, @folder)) as folder,
            IF(@items>100,@items:=0,@items) as checkItems
        FROM foo
    ) AA ORDER BY folder,num_items
) A;

Here is it execution:

mysql> SET @order_val = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @folder = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @curr_folder = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @items = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT *,
    ->     @order_val:=IF(folder=@curr_folder,@order_val+1,1) ordervalue,
    ->     @curr_folder:=folder current_folder
    -> FROM
    -> (
    ->     SELECT * FROM
    ->     (
    ->         SELECT
    ->             id, num_items,
    ->             (SELECT IF(((@items:=@items+num_items)>100),
    ->             @folder:=@folder+1, @folder)) as folder,
    ->             IF(@items>100,@items:=0,@items) as checkItems
    ->         FROM foo
    ->     ) AA ORDER BY folder,num_items
    -> ) A;
+----+-----------+--------+------------+------------+----------------+
| id | num_items | folder | checkItems | ordervalue | current_folder |
+----+-----------+--------+------------+------------+----------------+
|  1 |         4 |      1 |          4 |          1 |              1 |
|  2 |        33 |      1 |         37 |          2 |              1 |
|  5 |        24 |      2 |         68 |          1 |              2 |
|  4 |        44 |      2 |         44 |          2 |              2 |
|  3 |        74 |      2 |          0 |          3 |              2 |
|  6 |        34 |      3 |          0 |          1 |              3 |
|  7 |        46 |      3 |         46 |          2 |              3 |
|  9 |        11 |      4 |         11 |          1 |              4 |
|  8 |        55 |      4 |          0 |          2 |              4 |
+----+-----------+--------+------------+------------+----------------+
9 rows in set (0.00 sec)

mysql>

Give it a Try !!!

Postgresql – String replace using concatenated strings from various columns

Depending on how you would want to deal with possible NULL values, concat_ws() is probably your safest and simplest way to go:

UPDATE tbl
SET filed1 = replace(field1, concat_ws(' ', field2, field3, field4), '')
WHERE filed1 IS DISTINCT FROM replace(field1, concat_ws(' ', field2, field3, field4), '')

concat_ws() ignores NULL values. With plain concatenation (||), one NULL field would make the whole pattern NULL. It was introduced with Postgres 9.1.

The added WHERE clause prevents empty updates. This enhances performance a lot if many rows wouldn't change anyway.

Best Answer

Related Solutions

Mysql – How to re-arrange rows to best fit within groups

UPDATE 2013-01-09 12:52 EDT

Postgresql – String replace using concatenated strings from various columns

Related Question