CODEWITHSUNDEEP
mysql
ADIN
ADIN

Reputation: 317

Query: Check previous row value

I have query like this

   SELECT * FROM `message` WHERE sender = 10
    ORDER BY `message`.`date` AND `message`.`topic` ASC

This query displays every messages. I want to select only the first row of specific topic with the earliest date.

Is there any way to solve this by sql way?

Upvotes: 0

Views: 42

Answers (1)

wvdz
wvdz

Reputation: 16651

If you just want to display the earliest date, and no other information in this row, it can be done very concise:

   SELECT topic, MIN(date)
   FROM message
   WHERE sender = ?
   GROUP BY topic;

If you want to display the whole row, it's a little more verbose. This example assumes that (sender,topic,date) is unique, if it isn't you need to do some extra work getting rid of duplicates.

WITH minDate AS
(
     SELECT sender, topic, MIN(date) AS date
     FROM message
     GROUP BY sender, topic;
)
SELECT m.*
FROM message m
JOIN minDate d ON m.topic = d.topic AND m.date = d.date AND d.sender = m.sender
WHERE sender = ?;

Upvotes: 1

Related Questions