CODEWITHSUNDEEP
phpmysql
Ben Greene
Ben Greene

Reputation: 147

mysql query for the latest 10 results in a single query

okay, my problem is a little bit more difficult than the title would tell...

i have a table with messages, i load all these messages in php using a while loop.

my sql query looks like this at the moment:

"SELECT * 
 FROM messages 
 WHERE thread_id = '" . $thread_id . "'
 ORDER BY date_sent"

works fine and returns all the messages after one another, so that i have the latest message at the bottom (which is important, since my messaging module works like facebook, with a small reply field at the bottom, to let the users chat directly)

now, what i'd like to have is a query that would give me only the latest 10 results in a single query.

i already have the number of wanted results ($number_of_results = 10;)

it'd be really cool, if i didn't have to count the rows of the result first, but rather would request the result in a single query.

something like:

"SELECT * 
 FROM messages 
 WHERE thread_id = '" . $thread_id . "'
 ORDER BY date_sent 
 LIMIT TOTAL_NUMBER_OF_ROWS_WHERE_THREAD_ID_IS_MY_THREAD_ID-$number_of_results, $number_of_results"

is there a way to do something like this...?

Upvotes: 2

Views: 3096

Answers (6)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

"SELECT * 
 FROM messages 
 WHERE thread_id = '" . $thread_id . "' 
 ORDER BY date_sent DESC 
 LIMIT 10";

Make sure you have this index

ALTER TABLE messages 
ADD INDEX threadid_datesent_ndx( thread_id, date_sent );

If you want the 10 reversed again, then do this:

"SELECT * 
 FROM
 (
     SELECT * 
     FROM messages 
     WHERE thread_id = '" . $thread_id . "' 
     ORDER BY date_sent DESC LIMIT 10
 ) A
 ORDER BY date_sent";

Upvotes: 6

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

Something like

Select * From 
  (Select top(10) * From Messages Where threadid = ? order by date_sent desc) dummyName
Order By date_sent asc

I think.

Upvotes: 0

Alexander Gharibashvili
Alexander Gharibashvili

Reputation: 983

Rolando is right. You must order records backward and after this limit results.

Upvotes: 0

Ami
Ami

Reputation: 1254

LIMIT limits the result set, and SQL_CALC_FOUND_ROWS counts the number of rows that would have been returned without the LIMIT.

SELECT SQL_CALC_FOUND_ROWS *
FROM messages
WHERE thread_id = :thread_id
ORDER BY date_sent DESC
LIMIT 10

Then use mysql_num_row() to get the count.

Upvotes: 5

ludaavics
ludaavics

Reputation: 678

Order the dates the other way around?

"SELECT * FROM messages WHERE thread_id='".$thread_id."' ORDER BY date_sent DESC LIMIT ".$number_of_results

Upvotes: 0

Madbreaks
Madbreaks

Reputation: 19539

Let's avoid the loop altogether:

$threadIds = array(100,101,102); // etc

$result = mysql_query("SELECT * FROM messages WHERE thread_id IN (".implode(',', $threadIds).") ORDER BY date_sent DESC LIMIT 10");

That should work. Note that this might be less efficient depending on your data model and the amount of data you're working with.

EDIT

This will give you the OVERALL most recent 10. If you need the most recent 10 for each thread_id, check out RolandoMySQLDBA's answer.

Cheers

Upvotes: 0

Related Questions