CODEWITHSUNDEEP
phpmysql
user2310422
user2310422

Reputation: 555

How to select the last row in mysql

I created a table which contains at least 100 rows. When I want to retrieve an element, I do the following code below. This will return the items which has an id between 11 and 28. My goal is to be able to echo the last items which is 28. How can I achieve this on mysql? 

SELECT names
FROM item_list
WHERE id >= 10 
LIMIT 18

Upvotes: 2

Views: 211

Answers (3)

James Green
James Green

Reputation: 1753

It depends if this is really what you want to do, but these options might be close:

SELECT names FROM item_list WHERE id = 28

or...

SELECT names FROM item_list WHERE id <= 28 ORDER BY id DESC LIMIT 1

or even...

SELECT * FROM ( SELECT names FROM item_list WHERE id >= 10 LIMIT 18 ORDER BY id DESC ) LIMIT 1

One more (cleaner than the previous, but longer), for fun:

SELECT names 
    FROM ( 
        SELECT id, names
        FROM item_list 
        WHERE id >= 10
        ORDER BY id
        LIMIT 18 ) subQ
ORDER BY id DESC 
LIMIT 1

It isn't 100% clear what you /actually/ want to get as your list changes -- but hopefully these examples will steer you to the right bits of documentation!

Upvotes: 2

Manikandan S
Manikandan S

Reputation: 922

You can do using order by with limit in mysql.

select * from table_name order by id desc limit 0,1

Here id is the auto increment value.

Upvotes: 0

Kermit
Kermit

Reputation: 34054

Your original query will actually return rows 10 through 27.

| ID |
------
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
| 22 |
| 23 |
| 24 |
| 25 |
| 26 |
| 27 |

To get the 28th row, you can use

WHERE id >= 10 
LIMIT 18, 1

See a demo

Or just

LIMIT 27, 1

See a demo

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement.

 SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15
 SELECT * FROM tbl LIMIT 95,18446744073709551615;  # Retrieves rows from the 96th row to the last

In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.

Upvotes: 7

Related Questions