Search

Top N 쿼리

Top N 쿼리

κ²°κ³Ό μ§‘ν•©μ—μ„œ μƒμœ„ N개의 행을 λ°˜ν™˜ν•˜λŠ” 쿼리

μ’…λ₯˜

β€’
ROWNUM
β€’
ROWID

ROWNUM

SELECT 문의 논리적인 μˆœλ²ˆμ„ λ‚˜νƒ€λ‚΄λŠ” κ°€μƒμ˜ 컬럼
ORACLE DB
데이터λ₯Ό 좜λ ₯ν•  λ•Œ λΆ€μ—¬λ˜λŠ” 논리적인 μˆœμ„œ 번호이기 λ•Œλ¬Έμ—, ROWNUM 을 μ‚¬μš©ν•˜μ—¬ νŽ˜μ΄μ§• 처리λ₯Ό ν•˜κΈ° μœ„ν•΄μ„œλŠ” 인라인 λ·°λ₯Ό μ¨μ•Όν•œλ‹€.
ROWNUM λ₯Ό ν™œμš©ν•œ νŽ˜μ΄μ§• 처리
SELECT * FROM ( SELECT ROWNUM AS row_num, no, title, content FROM board WHERE ROWNUM <= 10 -- μ›ν•˜λŠ” νŽ˜μ΄μ§€ 크기λ₯Ό 여기에 μ§€μ •ν•©λ‹ˆλ‹€. ) WHERE row_num >= 1; -- μ›ν•˜λŠ” νŽ˜μ΄μ§€ λ²ˆν˜Έμ— 맞게 μ‹œμž‘ν•˜λŠ” ν–‰ 번호λ₯Ό 여기에 μ§€μ •ν•©λ‹ˆλ‹€.
SQL
볡사

board ν…Œμ΄λΈ” 생성 및 μƒ˜ν”Œ 데이터

-- DDL CREATE TABLE board ( no NUMBER NOT NULL, title varchar2(100) NOT NULL, writer varchar2(100) NOT NULL, content varchar2(1000), reg_date DATE DEFAULT sysdate NOT NULL, upd_date DATE DEFAULT sysdate NOT NULL, views NUMBER DEFAULT 0 NOT NULL, PRIMARY KEY (no) ); TRUNCATE board; -- μƒ˜ν”Œ 데이터 INSERT INTO `board` (title, writer, content) VALUES ('제λͺ© 1', 'μž‘μ„±μž 1', 'λ‚΄μš© 1'), ('제λͺ© 2', 'μž‘μ„±μž 2', 'λ‚΄μš© 2'), ('제λͺ© 3', 'μž‘μ„±μž 3', 'λ‚΄μš© 3'), ('제λͺ© 4', 'μž‘μ„±μž 4', 'λ‚΄μš© 4'), ('제λͺ© 5', 'μž‘μ„±μž 5', 'λ‚΄μš© 5'), ('제λͺ© 6', 'μž‘μ„±μž 6', 'λ‚΄μš© 6'), ('제λͺ© 7', 'μž‘μ„±μž 7', 'λ‚΄μš© 7'), ('제λͺ© 8', 'μž‘μ„±μž 8', 'λ‚΄μš© 8'), ('제λͺ© 9', 'μž‘μ„±μž 9', 'λ‚΄μš© 9'), ('제λͺ© 10', 'μž‘μ„±μž 10', 'λ‚΄μš© 10'), ('제λͺ© 11', 'μž‘μ„±μž 11', 'λ‚΄μš© 11'), ('제λͺ© 12', 'μž‘μ„±μž 12', 'λ‚΄μš© 12'), ('제λͺ© 13', 'μž‘μ„±μž 13', 'λ‚΄μš© 13'), ('제λͺ© 14', 'μž‘μ„±μž 14', 'λ‚΄μš© 14'), ('제λͺ© 15', 'μž‘μ„±μž 15', 'λ‚΄μš© 15'), ('제λͺ© 16', 'μž‘μ„±μž 16', 'λ‚΄μš© 16'), ('제λͺ© 17', 'μž‘μ„±μž 17', 'λ‚΄μš© 17'), ('제λͺ© 18', 'μž‘μ„±μž 18', 'λ‚΄μš© 18'), ('제λͺ© 19', 'μž‘μ„±μž 19', 'λ‚΄μš© 19'), ('제λͺ© 20', 'μž‘μ„±μž 20', 'λ‚΄μš© 20'), ('제λͺ© 21', 'μž‘μ„±μž 21', 'λ‚΄μš© 21'), ('제λͺ© 22', 'μž‘μ„±μž 22', 'λ‚΄μš© 22'), ('제λͺ© 23', 'μž‘μ„±μž 23', 'λ‚΄μš© 23'), ('제λͺ© 24', 'μž‘μ„±μž 24', 'λ‚΄μš© 24'), ('제λͺ© 25', 'μž‘μ„±μž 25', 'λ‚΄μš© 25'), ('제λͺ© 26', 'μž‘μ„±μž 26', 'λ‚΄μš© 26'), ('제λͺ© 27', 'μž‘μ„±μž 27', 'λ‚΄μš© 27'), ('제λͺ© 28', 'μž‘μ„±μž 28', 'λ‚΄μš© 28'), ('제λͺ© 29', 'μž‘μ„±μž 29', 'λ‚΄μš© 29'), ('제λͺ© 30', 'μž‘μ„±μž 30', 'λ‚΄μš© 30'), ('제λͺ© 31', 'μž‘μ„±μž 31', 'λ‚΄μš© 31'), ('제λͺ© 32', 'μž‘μ„±μž 32', 'λ‚΄μš© 32'), ('제λͺ© 33', 'μž‘μ„±μž 33', 'λ‚΄μš© 33'), ('제λͺ© 34', 'μž‘μ„±μž 34', 'λ‚΄μš© 34'), ('제λͺ© 35', 'μž‘μ„±μž 35', 'λ‚΄μš© 35'), ('제λͺ© 36', 'μž‘μ„±μž 36', 'λ‚΄μš© 36'), ('제λͺ© 37', 'μž‘μ„±μž 37', 'λ‚΄μš© 37'), ('제λͺ© 38', 'μž‘μ„±μž 38', 'λ‚΄μš© 38'), ('제λͺ© 39', 'μž‘μ„±μž 39', 'λ‚΄μš© 39'), ('제λͺ© 40', 'μž‘μ„±μž 40', 'λ‚΄μš© 40'), ('제λͺ© 41', 'μž‘μ„±μž 41', 'λ‚΄μš© 41'), ('제λͺ© 42', 'μž‘μ„±μž 42', 'λ‚΄μš© 42'), ('제λͺ© 43', 'μž‘μ„±μž 43', 'λ‚΄μš© 43'), ('제λͺ© 44', 'μž‘μ„±μž 44', 'λ‚΄μš© 44'), ('제λͺ© 45', 'μž‘μ„±μž 45', 'λ‚΄μš© 45'), ('제λͺ© 46', 'μž‘μ„±μž 46', 'λ‚΄μš© 46'), ('제λͺ© 47', 'μž‘μ„±μž 47', 'λ‚΄μš© 47'), ('제λͺ© 48', 'μž‘μ„±μž 48', 'λ‚΄μš© 48'), ('제λͺ© 49', 'μž‘μ„±μž 49', 'λ‚΄μš© 49'), ('제λͺ© 50', 'μž‘μ„±μž 50', 'λ‚΄μš© 50');
SQL
볡사

ROWID

각 행을 κ³ μœ ν•˜κ²Œ μ‹λ³„ν•˜λŠ”λ° μ‚¬μš©λ˜λŠ” μ‹λ³„μž
ꡬ성 μš”μ†Œ
μ„€λͺ…
였브젝트 번호
ROWIDκ°€ μ†ν•œ 객체(ν…Œμ΄λΈ” λ˜λŠ” ν΄λŸ¬μŠ€ν„°)의 κ³ μœ ν•œ 번호λ₯Ό λ‚˜νƒ€λƒ…λ‹ˆλ‹€.
μƒλŒ€ 파일 번호
행이 μ €μž₯된 μƒλŒ€μ μΈ 데이터 파일 번호λ₯Ό λ‚˜νƒ€λƒ…λ‹ˆλ‹€.
블둝 번호
행이 μ €μž₯된 데이터 λΈ”λ‘μ˜ 번호λ₯Ό λ‚˜νƒ€λƒ…λ‹ˆλ‹€.
데이터 번호
블둝 λ‚΄μ—μ„œμ˜ ν–‰μ˜ μƒλŒ€μ μΈ μœ„μΉ˜λ₯Ό λ‚˜νƒ€λƒ…λ‹ˆλ‹€.
β€’
ROWID μ˜ˆμ‹œμ½”λ“œ
SELECT ROWID , employee_id , first_name FROM employees WHERE department_id = 30;
SQL
볡사