DB 쿼리 최신순 1건 조회하기

단건 조회 

1. limit 이용하기
SELECT *
FROM document_status
WHERE doc_uid = {document_uid}
ORDER BY docsta_created_at DESC
LIMIT 1;

2. distinct 이용하기
SELECT DISTINCT ON (doc_uid) *
FROM document_status
WHERE doc_uid = {document_uid}
ORDER BY doc_uid DESC, docsta_created_at DESC;

 

1번째 방법이 더 빠름. 

2번째 방법의 order by에는 disctinct에 들어가는 컬럼이 포함되어야 함.

 

SQLAlchemy 

            stmt = (
                select(DocumentStatus)
                .where(DocumentStatus.doc_uid == document_uid)
                .distinct(DocumentStatus.doc_uid)
                .order_by(
                    desc(DocumentStatus.doc_uid), desc(DocumentStatus.docsta_created_at)
                )
            )

            stmt = (
                select(DocumentStatus)
                .where(DocumentStatus.doc_uid == document_uid)
                .order_by(desc(DocumentStatus.docsta_created_at))
                .limit(1)
            )

여러 건 조회

SELECT DISTINCT ON (document_status.doc_uid) *
FROM document_status
WHERE document_status.doc_uid in ()
ORDER BY document_status.doc_uid DESC, document_status.docsta_created_at DESC;

SQLAlchemy

            stmt = (
                select(DocumentStatus)
                .distinct(DocumentStatus.doc_uid)
                .where(DocumentStatus.doc_uid.in_(document_uids))
                .order_by(
                    DocumentStatus.doc_uid,
                    desc(DocumentStatus.docsta_created_at),
                )
            )