[MSSQL] MAX ROW 가져오기 (최대값을 가진 ROW들을 가져오는 법)

 

데이터 추출을 하다보면 최신 데이터 기준의 전체 행의 데이터를 체크해야하거나 하는 일들이 종종 있을 때에 사용하기 좋은 함수에 쿼리를 활용하는 방법을 확인하도록 하자.

TEST Table & Data Create

CREATE TABLE #tmp_data
(
	cust_id int,
	cust_name varchar(10),
	cust_date varchar(10),
	cust_eng int,
	cust_math int
)

INSERT INTO #tmp_data VALUES (1,'홍길동','2024-01-01',70,85)
INSERT INTO #tmp_data VALUES (2,'홍길동','2024-02-01',75,95)
INSERT INTO #tmp_data VALUES (3,'홍길동','2024-03-01',80,80)
INSERT INTO #tmp_data VALUES (4,'임꺽정','2024-01-01',100,80)
INSERT INTO #tmp_data VALUES (5,'임꺽정','2024-02-01',97,83)
INSERT INTO #tmp_data VALUES (6,'임꺽정','2024-03-01',100,78)
INSERT INTO #tmp_data VALUES (7,'임꺽정','2024-04-01',88,92)

 

최근 시험 점수 가져오기

SELECT *
  FROM #tmp_data
 WHERE cust_date = ( SELECT MAX(cust_date) FROM #tmp_data )

 

위와 같이 단순히 시험일자 ( cust_date ) 의 MAX 값을 구하게 되면 아래와 같이 홍길동의 마지막 시험 점수가 누락되어 조회된다.

해당 테이블의 데이터에서 가장 마지막 일자는 [ 2024-04-01 ] 이기 때문이다.

홍길동의 마지막 시험점수가 누락되어 조회

 

 

이에 아래와 같이 ROW_NUMBER OVER 를 이용하여 이름별 날짜의 순위를 메겨 조회를 하면 시험친 학생들의 학생별 마지막 시험일의 시험점수를 조회할 수 있다.

SELECT  *
  FROM (
		SELECT *,
			ROW_NUMBER() OVER(PARTITION BY cust_name ORDER BY cust_date desc) AS num 
			FROM #tmp_data
	    ) AS A
 WHERE num = 1

학생별 마지막 시험일자의 시험점수 조회

 

 

ROW_NUMBER() OVER(PARTITION BY cust_name ORDER BY cust_date desc) 를 이용하면 학생별로 분할된 번호가 메겨지게 되는데 order by cust_date desc 를 이용하여 역순으로 마지막 일자를 무조건 1번으로 데이터를 지정하기 때문에 where num = 1 을 통해 학생들의 마지막 시험일자의 시험점수를 구할 수 있게 된다.

SELECT *,
	  ROW_NUMBER() OVER(PARTITION BY cust_name ORDER BY cust_date desc) AS num 
FROM #tmp_data

학생들의 시험일자별 row의 num 항목에 순서를 메겨서 조회