CODEWITHSUNDEEP
phpmysqlsql-serverdatabase
Fady
Fady

Reputation: 211

mysql - selecting after sorting

I have a table that looks like

 ID |value
 --------
 1  |  10
 --------
 2  |  30
 --------
 3  |  20
 --------
 4  |  20
 --------
 5  | 10 
 --------

I sort it in descending order and selected first two rows using LIMIT.

so the selection query returns :

 --------
 2  |  30
 --------
 3  |  20
 --------

Now I want to select the second two rows in the same way of sorting so the result should be :

 --------
 4  |  20
 --------
 1  |  10
 --------

How can I do this ?

The query must be a dynamic query I mean after selection those two rows I should select the next two rows and so on until the end of the table.

Important Declaration

The query must take last ID from the previous selection and generate the next two automatically. Sorry, I forget to say that at the first time I posted the question.

Upvotes: 1

Views: 292

Answers (3)

newfurniturey
newfurniturey

Reputation: 38456

You can use the OFFSET portion of the LIMIT clause:

LIMIT 2 OFFSET 2;

This will allow you to limit the results starting after the specified offset, in this case - after the second row.

UPDATE (dynamic query?)
To make the select/offset dynamic, you'll need to handle that portion in PHP. I'm assuming that you're pulling the records, doing something with them, then pulling the next set? If this is correct, you can just keep a counter for how many iterations you've done:

$offsetCounter = 0;
// start your loop, based on whatever you need (for, while, etc.)
    $limit = 'LIMIT 2 OFFSET ' . (2 * $offsetCounter++);
    // append `$limit` to the end of your query
    $query = 'SELECT ID, value FROM yourTable ORDER BY value DESC ' . $limit;

UPDATE #2 (dynamic query, without OFFSET)
If you can't use OFFSET, or at least a counter to implement it with, but you can use the ID of the last record retrieved you can add that into your WHERE clause (I would also recommend sorting by the ID field too in that case).

Something like the following should work (update to use prepared-statements if necessary):

$lastIdSelected = 0;
// start your loop
    $query = 'SELECT ID, value FROM yourTable';
    $query .= (($lastIdSelected != 0) ? ' WHERE id > ' . $lastIdSelected : '');
    $query .= ' ORDER BY value DESC, id ASC LIMIT 2';
    // execute query, do your work, etc.

    // on your last record, do:
    $lastIdSelected = $row['ID'];

Of course, this also follows the format of my first-update with the OFFSET variable. You can modify this to not use a temp-variable to store the last ID, but it may end up being the same amount of work either way.

Upvotes: 2

Nikolaos Dimopoulos
Nikolaos Dimopoulos

Reputation: 11485

You can use the OFFSET

$sql = 'SELECT ID, value FROM myTable LIMIT 0, 2 ORDER BY value DESC;

The above will give you the first two rows.

$sql = 'SELECT ID, value FROM myTable LIMIT 2, 2 ORDER BY value DESC;

This will give you the next two rows

More information on LIMIT can be found here

Upvotes: 1

Steve
Steve

Reputation: 3046

use LIMIT 2, 2

Read more about LIMIT here: http://php.about.com/od/mysqlcommands/g/Limit_sql.htm

Upvotes: 2

Related Questions