Return the nth record from MySQL query

I am looking to return the 2nd, or 3rd, or 4th record from a MySQL q开发者_如何学Gouery (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.

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

It says return one record starting at record n.

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

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

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

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

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