MYSQL Select from table, get newest/last 10 rows in table

Question

What's the best, and easiest way to do this? My query currently is:

  SELECT * 
    FROM chat 
   WHERE (userID = $session AND toID = $friendID) 
      OR (userID = $friendID AND toID = $session) 
ORDER BY id 
   LIMIT 10

This shows the first 10 rows though, not the last 10.

EDIT: I Want the last 10 rows (Which yes, DESC does this) However I want them to be returned in ASCENDING order.

Use the **sort** option on the database. – user631756 Feb 24 '12 at 02:01 — user631756, Feb 24 '12 at 02:01

SimonMayer · Accepted Answer · 2012-02-24T02:08:38.123

29

to reverse the order (therefore get last 10 instead of first 10), use DESC instead of ASC

EDIT

Based on your comment:

SELECT * FROM (
  SELECT * 
  FROM chat 
  WHERE (userID = $session AND toID = $friendID) 
    OR (userID = $friendID AND toID = $session)  
  ORDER BY id DESC
  LIMIT 10
) AS `table` ORDER by id ASC

edited Feb 24 '12 at 02:08

answered Feb 24 '12 at 02:01

SimonMayer

4,719
4
33
45

1

I forgot to include in my question, I want the last 10 rows (yes using DESC), but in Asc order. – Dylan Cross Feb 24 '12 at 02:05
I know this is an old post, but this is just what I was looking for. Thanks :) – Jeremy Board Sep 19 '15 at 11:26

score 1 · Answer 2 · answered Dec 07 '19 at 00:38

1

First select the last 10 from the table, then re-order them in ascending order.

SELECT * FROM (SELECT * FROM table ORDER BY id DESC LIMIT 10) sub ORDER BY id ASC

answered Dec 07 '19 at 00:38

Baz

41
3

score 1 · Answer 3 · answered Feb 24 '12 at 02:01

1

If you want the last 10 then just change ASC to DESC

SELECT * 
FROM 
chat 
WHERE 
(userID=$session AND toID=$friendID) 
OR 
(userID=$friendID AND toID=$session) 
ORDER BY id 
DESC
LIMIT 10

answered Feb 24 '12 at 02:01

romainberger

4,563
2
35
51

Oops, I forgot to include in my question, the last 10 (which yes can be done with DESC, but in ASC order. – Dylan Cross Feb 24 '12 at 02:04

mohitesachin217 · Answer 4 · 2019-09-13T05:24:15.047

0

$con = mysqli_connect("localhost","my_user","my_password","my_db");
$limit = 10;                
$query = "SELECT * FROM  $table";
$resource = mysqli_query($con,$query);
$total_rows = mysqli_num_rows($resource);
$start = $total_rows-$limit;
$query_limit= $query." LIMIT $start,$limit";

First I have set the limit

$limit = 10;

then

 $total_rows = mysqli_num_rows($resource);

Here I have taken total number of rows affected.

$start = $total_rows-$limit;

then substracted limit from number of rows to take starting record number

   $query_limit= $query." LIMIT $start,$limit";

and then added limit to the query. For more information about limit see this link https://www.w3schools.com/php/php_mysql_select_limit.asp

edited Sep 13 '19 at 05:24

answered Nov 05 '14 at 00:47

mohitesachin217

451
5
14

1

Code dumps without explanation are rarely helpful. Please add some context to your answer. – ChrisGPT was on strike Nov 05 '14 at 01:13
This could be an issue if you have a filter. some data could be ignored. – Asuquo12 Sep 13 '17 at 07:50
@Chris Is it okay...? I have added little explanation to the answer – mohitesachin217 Oct 11 '17 at 14:39
1

@mohitesachin217, you've added some explanation, but you're using the ancient `mysql_*` functions in your answer. If you're writing new code, **_please_ don't use the `mysql_*` functions**. They are old and broken, were deprecated in PHP 5.5 (which is so old it no longer even receives security updates), and completely removed in PHP 7. Use [`PDO`](https://secure.php.net/manual/en/book.pdo.php) or [`mysqli_*`](https://secure.php.net/manual/en/book.mysqli.php) with _prepared statements_ and _parameter binding_ instead. See http://stackoverflow.com/q/12859942/354577 for details. – ChrisGPT was on strike Oct 11 '17 at 15:06

MYSQL Select from table, get newest/last 10 rows in table

4 Answers4

Linked