MySQL Error

쇼핑몰 - 회원의 최근 배송지 목록 조회하기

 

⬇️ payments 테이블의 데이터 

⬇️ SQL

SELECT DISTINCT
	receivername, receiverphone, postcode, addr1, addr2	
FROM payments
WHERE memberid = 2
ORDER BY date DESC
LIMIT 5;

 

⬇️ Error

[ERROR] 24-11-28 20:05:03 [sqlonly:144] - 1. PreparedStatement.execute() SELECT DISTINCT

receivername, receiverphone, postcode, addr1, addr2

FROM payments

WHERE memberid = 2 AND paycheck = 'Y'

ORDER BY date DESC

LIMIT 5

java.sql.SQLException: Expression #1 of ORDER BY clause is not in SELECT list, references column 'sony.payments.date' which is not in SELECT list; this is incompatible with DISTINCT

 

⬇️ 원인

ORDER BY 절에서 사용된 date 가 SELECT 목록에 포함되지 않았기 때문이다

DISTINCT와 ORDER BY를 함께 사용할 때, ORDER BY에 사용된 모든 칼럼이 SELECT 목록에 포함돼야 한다

 

 

✨ 최종

SELECT DISTINCT 
	receivername, receiverphone, postcode, addr1, addr2, date 
FROM payments 
WHERE memberid = 2 AND paycheck = 'Y' 
ORDER BY date DESC 
LIMIT 5;

 

|-------------|--------------|---------|------------|------|--------------------|
|receivername |receiverphone |postcode |addr1       |addr2 |date                |
|-------------|--------------|---------|------------|------|--------------------|
|신민재          |01034563456   |90876    |서울시 강남구 역삼동 |404호  |2024-11-28 19:51:17 |
|문성주          |01034563456   |45645    |서울시 강남구 잠실동 |8호    |2024-11-28 19:50:15 |
|-------------|--------------|---------|------------|------|--------------------|