only get one last record with the same field value during sorting database records

Farhad Eft

Hi, I have a table in database for messages and it has these fields: MessageId / SenderId / Message / Date I'd like to create a list of messages but only show the latest message from each user not listing whole messages just like how facebook shows messages. Is there a way to get the exact result by a sql query or I have to first get all the data from the database then build the list I want? Thank you

Wendelius

You can do this (at least partially) in the db. For example:

SELECT ...
SUBSTRING(m.Message,1, 80) AS PartialMessage,
...
FROM Messages m
WHERE m.MessageID IN (SELECT MAX(m2.MessageId)
FROM Messages m2
GROUP BY m2.SenderId)

The above (with typos corrected etc:)) should fetch the latest messsages for each sender and bring only first 80 characters of the message. In the example I'm relying on a fact that the MessageId field is constantly growing number so it will order the messages correctly. If this is not true then you would have to use the date field to get the latest messages.

The need to optimize rises from a bad design.My articles[^]

Viral Upadhyay

Farhad Eft wrote:

Is there a way to get the exact result by a sql query

Yes if you want sorted data then use 'order by' statement. if you want some condition data then use 'where' condition. No need to get all the data from database and then build list.

Viral My Blog Save Our Tigers