SQLで、GROUP BYしてグループ化後に、最新(created_at)のみ取得するクエリについて

Question

お世話になります。

SQLのクエリで、自己解決できない問題があり、質問させていただきました。

テーブル構造
user ユーザテーブル
id, name
work 仕事テーブル
id, name
user_order_work 応募管理テーブル
id, user_id, work_id, createdAt

※応募は、同じユーザが同じ仕事になんども投稿できるとする。

問題
上記のテーブル構造から、

「ある仕事(work.id=1)の応募一覧を、応募したユーザが重複しない形で取得する。なおかつ、応募したユーザが重複している場合は、そのユーザが、最後に(最新の)応募した時の応募管理情報を取得する」

と言う問題を解決したいです。

SELECT * FROM user_order_work as uow left join work as w ON uow.work_id = w.id where w.id = 1 group by uow.user_id;

上記のクエリを書いてみたのですが、「ある仕事の応募一覧を、応募したユーザが重複しない形で取得する。」までは、クリアできます。しかし、「応募したユーザが重複している場合は、そのユーザが、最後に(最新の)応募した時の応募管理情報を取得する」がクリアできません。

どのようなクエリを書けば、この問題をクリアできますか？

よろしくお願い致します。

Comments

[KiyoshiMotoki]

お使いのDB製品（MySQL or PostgreSQL or ... など）を教えてください。それによって、解決策が変わる可能性がありますので。

Answer 1

IDも含めて全部データがほしいならしたみたいな感じで
最後の応募を求めてからJOINすればいいです

SELECT
	  USER_ORDER.*
FROM (
	SELECT
		  user_id
		, work_id
		, MAX(createdAt) createdAt
	FROM user_order_work
	WHERE work_id = 1
	GROUP BY
		  user_id
		, work_id
) LAST_ORDER
JOIN user_order_work USER_ORDER
ON USER_ORDER.user_id = LAST_ORDER.user_id
AND USER_ORDER.work_id = LAST_ORDER.work_id
AND USER_ORDER.createdAt = LAST_ORDER.createdAt

IDがいらないなら最後の応募を求めているLAST_ORDERの中だけで事足ります

Answer 2

select * の部分を以下のようにしてみてはいかがでしょう？

 select MAX(created_at), 取得したいカラム.... FROM ....

Comments

[usop]

超速のコメントありがとうございます。
select MAX(created_at)だと、created_atしかselectされません。
select *, MAX(created_at)だと、取得結果としては、user_order_workと、userの最新応募しては、紐付きますが、user_order_workのデータとしては、紐付かない形となります。
こう言った場合は、subQueryがいいのでしょうか

[ShoheiUyama]

SELECT MAX(uow.created_at), uow.work_id,... のように*を使用しない記法に変更してもダメでしょうか？

*を使いたいのであればサブクエリ化も考えたほうが良いかもしれませんね。