mysql - SQL Limit 和 Order By 跨连接

Question

我正在存储推文、推特用户，并将推特用户分组。

我有以下4张表

推文:

tweet_id | user_id | created
-------------------------------
23452345 | 2345    | 2013-08-12
23456094 | 1234    | 2014-03-24
23097777 | 1234    | 2014-04-12
23948798 | 9999    | 2013-09-22

推特用户:

user_id | screen_name
------------------------
2345    | michael
1234    | david
9999    | not_interested

推特社交:

user_id | social_id
---------------------------
2345    | 34
9999    | 20
1234    | 80

社交类别:

social_id | category_id
-----------------------
34        | 3
20        | 6
80        | 3

我想查看出现在特定社交类别中的每个用户的最旧推文。我编写的以下 SQL 似乎不起作用。每个 twitter_user 我有一行，但是我没有看到最早的推文

SELECT tu.screen_name as Handle, tw.created_at as Earliest
FROM twitter_users tu
    LEFT JOIN tweets tw
        ON tu.user_id = tw.user_id
    LEFT JOIN twitter_social ts
        ON ts.user_id = tu.user_id
    LEFT JOIN social_categories cs
        ON ts.social_id = cs.social_id
WHERE cs.category_id=3
GROUP BY Handle
ORDER BY Earliest ASC

编辑我希望得到如下结果

Handle  | Earliest
---------------------
david   | 2014-03-24
michael | 2013-08-12

Answer 1

SELECT tu.screen_name as Handle
    ,MIN(tw.created_at) as Earliest
FROM twitter_users tu
LEFT JOIN tweets tw
    ON tu.user_id = tw.user_id
LEFT JOIN twitter_social ts
    ON ts.user_id = tu.user_id
LEFT JOIN social_categories cs
    ON ts.social_id = cs.social_id
WHERE cs.category_id=3
GROUP BY tu.screen_name
ORDER BY Earliest ASC