特定カラムの重複なしで最新レコードを取得

SQL

備忘録メモメモ。

  • テーブルはarticleとcontents
  • 主キーはarticle.no
  • article.noContentsの重複をなくしたい
  • 最新レコードの判断はarticle.datetimesで判断
SELECT
A.no,
A.noContents,
A.datetimes,
A.title,
A.body,
A.aword,
A.flgOpen,
contents.name,
contents.aword as aword_contents,
contents.contents
FROM article AS A, contents
WHERE A.noContents = contents.no
AND   A.flgOpen    = 1
AND   NOT EXISTS
(
	SELECT no FROM article AS B
	WHERE A.datetimes  < B.datetimes
	AND   A.noContents = B.noContents
)
ORDER BY A.datetimes DESC

1テーブルでやる場合は

SELECT
no,
noContents,
datetimes,
title,
body,
aword,
flgOpen
FROM article AS A
WHERE NOT EXISTS
(
	SELECT no FROM article AS B
	WHERE A.datetimes  < B.datetimes
	AND   A.noContents = B.noContents
)
ORDER BY datetimes DESC