CODEWITHSUNDEEP
mysqlsqldatabasepostgresqlsqlite
Lior Elrom
Lior Elrom

Reputation: 20882

Selecting all records using SQL LIMIT and OFFSET query

I wonder if there is a way to accomplish:

SELECT * FROM table

by using LIMIT and OFFSET like so:

SELECT * FROM table LIMIT all OFFSET 0

Can I write SQL statement using LIMIT and OFFSET but still getting ALL results?

* Of course, I can use an IF statement, but I would rather avoid it if possible

Upvotes: 44

Views: 122270

Answers (7)

Lukasz Szozda
Lukasz Szozda

Reputation: 175964

Yes, it is possible by providing NULL:

SELECT * FROM tab LIMIT NULL OFFSET NULL

db<>fiddle PostgreSQL demo

7.6. LIMIT and OFFSET

LIMIT ALL is the same as omitting the LIMIT clause, as is LIMIT with a NULL argument.

Snowflake LIMIT / FETCH

The values NULL, empty string (''), and $$$$ are also accepted and are treated as “unlimited”; this is useful primarily for connectors and drivers (such as the JDBC driver) if they receive an incomplete parameter list when dynamically binding parameters to a statement.

SELECT * FROM demo1 ORDER BY i LIMIT NULL OFFSET NULL;

SELECT * FROM demo1 ORDER BY i LIMIT '' OFFSET '';

SELECT * FROM demo1 ORDER BY i LIMIT $$$$ OFFSET $$$$;

Upvotes: 13

Farid Rudiansyah
Farid Rudiansyah

Reputation: 31

As the record will grow up, use mysql_num_rows to dynamically find total amount of records, instead of using some large number.

$cektotalrec=mysql_query("SELECT * FROM TABLE");
$numoffset=mysql_num_rows($cektotalrec); 
$numlimit="0";

then:

$final="SELECT * FROM table ".$numlimit.", ".$numoffset"";

Upvotes: 2

Pritam Jana
Pritam Jana

Reputation: 296

I used this code in nodeJS with MySQL and it's run well, It's may help you. Why you use it?

  1. It's reliable because it's a string that will append with query.

  2. If you want to set limit then you can put the limitation with the variable otherwise pass 0 with variable. 

    var noOfGroupShow=0;  //0: all, rest according to number   
if (noOfGroupShow == 0) {
    noOfGroupShow = '';
} 
else {
    noOfGroupShow = ' LIMIT 0, '+noOfGroupShow;
}
var sqlGetUser = "SELECT `user_name`,`first_name`,`last_name`,`image`,`latitude`, `longitude`,`phone`,`gender`,`country`,`status_message`,`dob` as user_date_of_birth FROM `tb_user` WHERE `user_id`=?"+noOfGroupShow;

Upvotes: 3

Lesly Revenge
Lesly Revenge

Reputation: 922

  1. You might receive an error if you set the limit to a very high number as defined by mysql doc. Thereofre, you should try to limit it 9999999999999, going higher can give you an error unless you set up server to go higher.

  2. You might want to use LIMIT in a function, therefore it is not a bad idea. If you use it in a function, you might want it to be Limit All at one point and limit 1 at another point.

  3. Below, I list an example where you might want your application to have no limit.

function get_navigation($select = "*", $from= "pages", $visible= 1, $subject_id = 2, $order_by = "position", $sort_by = "asc", $offset=0, $limit = 9551615){ global $connection;

$query = " SELECT {$select} ";
$query .= " FROM {$from}  ";

$query .= " WHERE visible = {$visible} ";
$query .= " AND subject_id = {$subject_id} ";

$query .= " ORDER BY {$order_by}  {$sort_by} ";
$query .= " LIMIT {$offset}, {$limit} ";

mysqli_query($connection, $query);
$navigation_set = mysqli_query($connection, $query);
confirm_query($navigation_set);
return $navigation_set;


}

define ("SELECT", "*");
define ("FROM", "pages");
define ("VISIBLE", 1);
define ("SUBJECT_ID", 3);
define ("ORDER_BY", "position");
define ("SORT_BY", "ASC");
define ("LIMIT", "0");


$navigation_set = get_navigation(SELECT, FROM, VISIBLE, SUBJECT_ID, ORDER_BY, SORT_BY);

Upvotes: -1

Nate S.
Nate S.

Reputation: 69

This may not be the best way to do it, but its the first that comes to mind...

SELECT * FROM myTable LIMIT 0,1000000

Replace 1000000 with some adequately large number that you know will always be larger than the total number of records in the table.

Upvotes: 2

AgeDeO
AgeDeO

Reputation: 3147

Maybe not the cleanest solution but setting limit to a very high number could work. Offset needs to be 0.

Why not use a IF statement where you add the limit and offset to the query as a statement is true?

Upvotes: 1

sroes
sroes

Reputation: 15053

From the MySQL documentation:

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

So getting all rows might look as follows:

SELECT * FROM tbl LIMIT 0,18446744073709551615;

Upvotes: 45

Related Questions