MySQL 最大値のレコードの特定

質問

SQLが上手く作れないためお知恵をお貸し頂ければ幸いです。

articleテーブルに存在するユーザの一覧に、記事の投稿件数、記事に含まれるsizeの最大値、最大値が投稿された記事の登録日時、記事のタイトルを加えたものを抽出したいです。

[ article ]
article_id user_id size insert_unixtime title
----------------------------------------------------------
1 100 18 20130101 aaaaaaaaaaaa
2 100 10 20130102 bbbbbbbbbbbb
3 100 18 20130103 cccccccccccc
4 200 11 20130201 dddddddddddd

↓↓希望する抽出結果↓↓

article_id user_id count max_size insert_unixtime title
----------------------------------------------------------
3 100 3 18 20130103 cccccccccccc
4 200 1 11 20130201 dddddddddddd

自己結合等で、ユーザ毎の最大sizeを抽出してそれに該当するレコードを探すことであるていどはできたのですが
最大sizeが複数あった場合に、両方のレコードを取得してしまいます。（上記の例では、article_id 1と3を抽出してしまう。
※最大sizeが複数会った場合はinsert_unixtimeが大きい方のタイトルを表示したい。

うまく1SQLで抽出できないでしょうか。

回答No.2

root139

ユーザ毎の、article数とsizeの最大値、sizeの最大のものの登録日時とタイトルが取得したいという事ですよね？
下記の様なSQLで出来ると思います。(Ver. 5.5.8 で確認)

----------------------------------------
SELECT
article_id,
user_id,
(SELECT COUNT(*) FROM article c WHERE a.user_id = c.user_id) AS count,
size AS max_size,
insert_unixtime,
title
FROM article a
WHERE NOT EXISTS (
SELECT * FROM article b
WHERE a.user_id = b.user_id AND (a.size < b.size OR a.size = b.size AND a.insert_unixtime < b.insert_unixtime)
)
----------------------------------------

WHERE句では、user_id が同じで size が大きいものと同じ size で insert_unixtime が大きいものが無いという条件、つまり、その user_id で最大 size, insert_unixtime のレコードを取得する条件を書いています。

回答No.1

hardgeek

こういうことですか？
select article_id, user_id, size, insert_unixtime, title from article join (select max(article_id) as article_id from article where (user_id, size) in (select user_id, max(size) as size from article group by user_id) group by user_id) t using (article_id);

INサブクエリなら
select article_id, user_id, size, insert_unixtime, title from article where article_id in (select max(article_id) as article_id from article where (user_id, size) in (select user_id, max(size) as size from article group by user_id) group by user_id);