博士后:加入、分组、排序。我的问题错了吗?

CREATE TABLE IF NOT EXISTS members(
    group_id BIGINT, 
    amount INT, 
    updated_date timestamp)

CREATE TABLE IF NOT EXISTS supergroups_ref(
    group_id BIGINT PRIMARY KEY, 
    title TEXT DEFAULT NULL, 
    message_date timestamp)

CREATE TABLE IF NOT EXISTS supergroups(
    group_id BIGINT PRIMARY KEY, 
    lang TEXT DEFAULT NULL, 
    last_date timestamp DEFAULT NULL)

成员中的数据示例

(3, 90, date)
(1, 15, date)
(2, 200, date)
(1, 28, date)
(2, 300, date)
(1, 80, date)

supergroups_ref中的数据示例:

(1, 'title', date)
(2, 'title2', date)
(3, 'title3', date)

(1, 'en', date)
(2, 'it', date)
(3, 'it', date)

从表中,成员仅获取按“updated\u date”DESC排序的最新行,并在超群上左连接它们,以便超群中的每个group\u id都具有最新的成员数量。

SELECT sub.group_id, sub.amount, sub.title
FROM (
    SELECT s.group_id, m.amount, s_ref.title
    FROM  supergroups AS s
    LEFT OUTER JOIN members AS m
    ON m.group_id = s.group_id
    LEFT OUTER JOIN supergroups_ref AS s_ref
    ON s_ref.group_id = s.group_id
    ORDER BY m.updated_date DESC
) AS sub
GROUP BY sub.group_id, sub.amount, sub.title
ORDER BY sub.amount DESC

预期结果为: [(2, 200, 'title2'), (3, 90, 'title3'), (1, 15, 'title1')] 每个元素的第二个元素是最近的成员数量,因为该数量与日期一起存储在成员表中。元组是这样排序的