由Max通过第二个因素sqlite3的查询和过滤器 [英] sqlite3 query by max and filter by second factor

问题描述

我有：

TABLE MESSAGES
 message_id | conversation_id | from_user | timestamp  |  message

我想：

1. SELECT * WHERE from_user <> id 
2. GROUP BY conversation_id
3. SELECT in every group row with MAX(timestamp) **(if there are two same timestamps in a group use second factor as highest message_id)** !!!
4. then results SORT BY timestamp 

有结果：

2|145|xxx|10000|message

6|1743|yyy|999|message

7|14|bbb|899|message

通过消除

1|145|xxx|10000|message    <- has same timestamp(10000) as message(2) belongs to the same conversation(145) but message id is lowest  

5|1743|me|1200|message <- has message_from == me 

例如组相同的时间标记

example group with same timestamp

我想从这个小组第3行，但我得到的查询行2

i want from this group row 3 but i get row 2 from query

SELECT max(message_timestamp), message_id, message_text, message_conversationId
FROM MESSAGES
WHERE message_from <> 'me'
GROUP BY message_conversationId
ORDER by message_Timestamp DESC

什么是对我的脑海里，从与Message_ID和放大器做结合;时间戳然后得到最大???

what is on my mind to do union from message_id & timestamp and then get max???

推荐答案

尝试下面的SQL两倍达到你的目的按组。

Try below sql to achieve your purpose by group by twice.

select m.*
from
Messages m
-- 3. and then joining to get wanted output columns
inner join
(
    --2. then selecting from this max timestamp - and removing duplicates
    select conversation_id, max(timestamp), message_id
    from
    (
        -- 1. first select max message_id in remainings after the removal of duplicates from mix of cv_id & timestamp
        select conversation_id, timestamp, max(message_id) message_id
        from Messages
        where message <> 'me'
        group by conversation_id, timestamp
    ) max_mid
    group by conversation_id
) max_mid_ts on max_mid_ts.message_id = m.message_id
order by m.message_id;

http://goo.gl/MyZjyU

这篇关于由Max通过第二个因素sqlite3的查询和过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！