SQLで重複値を除去したものを抽出したい

質問

SQLで重複を除去したいのですが、DISTINCTやGROUP BYも検討したのですが、どうしても良い方法が見つからなかったので質問させて頂きます。

テーブルの構成は以下のようになっています。

roomsテーブル
id(INTEGER)
public(BOOLEAN)
updated_at(DATETIME)
created_at(DATETIME)

messagesテーブル
id(INTEGER)
sendfrom_list_id(INTEGER)
sendto_list_id(INTEGER)
room_id(INTEGER)※roomsテーブルへの外部キー
body(STRING)
updated_at(DATETIME)
created_at(DATETIME)

更に条件は以下のようになっています。

・アウトプットとして得たいのは
rooms.updated_at
rooms.id
rooms.public
messages.room_id
messages.sendfrom_list_id
messages.sendto_list_id
messagess.body
の7カラム。
・rooms.publicがTRUEである
・messages.room_id = rooms.idであること
・updated_atでDESCにソート

このような条件を満たすSQL文は以下のように書くことができました。

SELECT R.updated_at, R.id, R.public, M.room_id, M.sendfrom_list_id, M.sendto_list_id, M.body
FROM messages AS M, rooms AS R
WHERE R.public = "t" AND M.room_id = R.id
ORDER BY R.updated_at DESC;

しかし、ここに更に「rooms.idが重複しないもののみ抽出」という条件を加えたいのですが、どうしてもうまく行きません。

DISTINCTでは複数の中から一部のカラムだけをDISTINCTすることはできないようですし、GROUP BYも考えましたがどうやれば良いかわかりませんでした。

どなたか良い方法を教えて下さい。

回答No.2

Siegrune

「rooms.idが重複しないもののみ抽出」
ということなので、重複しているrooms.idのレコードは１件も出したくないのなら、

SELECT max(R.updated_at), R.id, max(R.public), max(M.room_id),
max(M.sendfrom_list_id), max(M.sendto_list_id), max(M.body)
FROM messages AS M, rooms AS R
WHERE R.public = "t" AND M.room_id = R.id
GROUP BY R.id
HAVING count(*) < 2
ORDER BY R.updated_at DESC;

ででると思うけど
・・・blobとかの型（つまりmax()を使えない型）の項目があればだめですが。⇒別のやり方有り。

回答No.1

todo36

cross apply で select top 1 かな。

http://stackoverflow.com/questions/16262312/sql-inner-join-with-top-1