[Resolved] Reverse Limit?

dbchip2000

Hi,
I am trying to create a query for a list of messages, so that the oldest come first. So the query so far is

SELECT * FROM messages ORDER BY sent

right? But then I want to limit the query so that I get one hundred of the most recent (still ordered by oldest on top). So, kind of like

[ oldest ]  = 25 rows     _
[   old  ]  = 25 rows      ||
[   um   ]  = 25 rows      ||
[   new  ]  = 25 rows      ||
[ newest ]  = 25 rows     _||

I don't know how many there are always going to be, so I can't just do LIMIT and a specific number and then 100.

It is kind of like "SELECT * FROM messages ORDER by sent", then reverse that, then add a LIMIT 0,100.

Thanks for your help

TheIceman5

SELECT * FROM messages ORDER BY sent DESC LIMIT 0,100

SELECT * FROM messages ORDER BY sent ASC LIMIT 0,100

what ever works the right way.

dbchip2000

No, the query I want is
SELECT FROM messages ORDER BY sent DESC;
Then say, I have 132 results, I want the bottom 100 so it would be
SELECT FROM messages ORDER BY sent DESC LIMIT 32,100;
But if the number of results changes, you have to change the initial. So, I want to know if that first number can be 'dynamic' or something like COUNT(id)-100.
It can't order it ASC, and then 0,100 because I have to output it so the oldest is first. Sorry if this is confusing, but thanks for help

SpeedySven

Once you have run your SQL query use the mysql_num_rows() function this will return the number of rows in your data set. So you want to display the rows between the highest number and the highest number - 100. if oyu see what I mean, so i guess.

$result = mysql_query("SELECT id FROM messages ORDER BY sent DESC;");

$total_rows = mysql_num_rows($result);
$limit_point = $total_rows - 100;

$result = mysql_query("SELECT * FROM messages ORDER BY sent DESC LIMIT $limit_point, $total_rows")

that should give you something like the desired effect.