CODEWITHSUNDEEP
mysql
paulrandall
paulrandall

Reputation: 663

Return the nth record from MySQL query

I am looking to return the 2nd, or 3rd, or 4th record from a MySQL query (based on a query by ID ascending)

The problem being, I won't know the ID, only that it is the 3rd row in the query.

Upvotes: 66

Views: 85542

Answers (6)

user4408343
user4408343

Reputation: 561

The accepted answer was wrong by 1 before the edit, because the offset is zero-indexed.

From the doc:

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

So the correct query would be

SELECT * FROM table ORDER BY ID LIMIT n-1,1

Upvotes: 38

Muhammad Abbas
Muhammad Abbas

Reputation: 129

MYSQL: The offset always start from zero-indexed

OFFSET value means not start from OFFSET value

Example: records 1, 2, 3, 4, 5.

OFFSET 1 means return 2nd value, as OFFSET 2 return 3rd value and so on

SELECT table_column FROM Table GROUP BY table_column DESC LIMIT 1 OFFSET 1;

OR

SELECT table_column FROM Table GROUP BY table_column DESC LIMIT 3 OFFSET 1;

It will return 3 records from 2nd record

Upvotes: 4

David Culbreth
David Culbreth

Reputation: 2796

If you are using PHP to process your records, then you might use the expressions from the PHP manual:

<?php
/* Open a connection */
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (!$link) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query = "SELECT Name, CountryCode FROM City ORDER BY Name";

if ($result = mysqli_query($link, $query)) {

    /* seek to row no. 400 */
    mysqli_data_seek($result, 399);

    /* fetch row */
    $row = mysqli_fetch_row($result);

    printf ("City: %s  Countrycode: %s\n", $row[0], $row[1]);

    /* free result set*/
    mysqli_free_result($result);
}

/* close connection */
mysqli_close($link);
?>

Read more in the PHP manual: http://php.net/manual/en/mysqli-result.data-seek.php

Upvotes: 1

Asesha George
Asesha George

Reputation: 2268

for example "LIMIT 10, 5", it will skip the number of records indicated by the first number and then show the number of records indicated by the second number. In other words it's "LIMIT skip, show".

SELECT * FROM tblTesting LIMIT 3, 6

will display from 4th record to 9th record, total records displayed 6

if you want show descending order use DESC

SELECT * FROM tblTesting ORDER BY column_name DESC LIMIT 3, 6

Upvotes: 10

cmptrgeekken
cmptrgeekken

Reputation: 8092

SELECT * FROM table ORDER BY ID LIMIT n-1,1

It says return one record starting at record n.

Upvotes: 125

Mike Cialowicz
Mike Cialowicz

Reputation: 10020

Use the limit clause (add 'limit 3, 1' to the end of your query to only select the third row).

Here's some more information: http://php.about.com/od/mysqlcommands/g/Limit_sql.htm

Upvotes: 6

Related Questions