Last 10 records?

mikesgt2

I am running a forum system.

I would like to select the LAST TEN posts in a particular thread, ordered by date ASC.

I am using MySql and all I have thought of is this that will not work.

SELECT
FROM f_posts
WHERE tid=2 / this is the thread number /
LIMIT (COUNT()-10),10;

Any ideas?

Anon

Hi ,
count will work only in select clause and not in where clause.
One way that i can suggest is , execute a separate query and get the count in a variable (say e.g. count) and then execute the above query as :
SELECT * FROM f_posts WHERE tid=2 LIMIT (COUNT-10),10;

The other way (which actually is a better way i feel )i can sugges is , execute the query as (/ assuming tdate is ur date field in the table /)
select * from f_posts where tid=2 order by tdate asc limit 0,10

Hope this answers ur query !!!

Shilpa

Anon

Shilpa is right about using a variable. It isn't the count you want, but the minimum date (the date of the 10th row sorted by date DESC). Then take this date, and sort ASC by rows with tdate >= than min_date.

SELECT @min_date:=tdate FROM f_posts WHERE tid=2 ORDER BY tdate DESC LIMIT 9,1;

SELECT * FROM f_posts WHERE tid=2 AND tdate >= @min_date;

BTW - SELECT * FROM f_posts WHERE tid=2 ORDER BY tdate ASC LIMIT 0,10 will get the first 10 posts, which isn't what you need here.

mikesgt2

Thanks that seems to work nicely.

mikesgt2

Why is that not provided for in mysql? Is it in other systems?

It could be done with the syntax:

select *
from f_posts
where tid=2
order by date asc
LIMIT -10;

Is it on their todo list?

Anon

-10 would be nifty, but I think subselects would solve this. Should be in mysql 4.1.