DB2 tutorial

https://www.db2tutorial.com/db2-basics/db2-select-distinct/
여기를 참고하였습니다.

OrderBy

select
	select_list
from
	table_name
Order by
	expression1[ASC]

SELECT 
	title, 
	isbn
FROM 
	books
ORDER BY 
	isbn NULLS FIRST;

NULLS FIRST => It treats NULL values as the lowest values:

ORDER BY 
	isbn NULLS LAST;

highest values by using the NULLS LAST option in the ORDER BY clause:

select -> from -> where -> orderby

SELECT
    select_list
FROM
    table_name
WHERE
    search_condition
ORDER BY
    sort_expression;

첨보는 표현.

SELECT
	book_id,
    title,
    LENGTH(title)
FROM
    books
ORDER BY
     3;

결과값:

Using Db2 WHERE clause to find rows whose values contain a string

SELECT    
    title, 
    total_pages, 
    rating, 
    published_date
FROM    
    books
WHERE title LIKE '%SQL%'
ORDER BY 
    title;

db2 WITH절. With문을 이용한 Recursive SQL

WITH n(level, empid, name) AS 
          (SELECT 1, empid, name 
             FROM emp
             WHERE name = 'Goyal'
           UNION ALL
           SELECT n.level + 1, nplus1.empid, nplus1.name 
             FROM emp as nplus1, n
             WHERE n.empid = nplus1.mgrid)
SELECT level, name FROM n;

LEVEL      NAME
----------- ----------
          1 Goyal
          2 Zander
          2 Henry
          2 Scott
          3 McKeough
          3 Barnes
          3 O'Neil
          3 Smith
          3 Shoeman

  9 record(s) selected

같은 예제 Oracle

SELECT LEVEL, name 
FROM emp
START WITH name = 'Goyal'
CONNECT BY PRIOR empid = mgrid

Fetch first ~ Rows ONLY

/* 조회된 결과에서 특정 갯수만 출력 */
WITH RECURSIVE_DATE ( NUM, DATE ) AS (
  SELECT 1 AS NUM
       , TO_CHAR(SYSDATE, 'YYYYMMDD') AS DATE
    FROM SYSIBM.SYSDUMMY1
   UNION ALL
  SELECT T1.NUM + 1 AS NUM
       , TO_CHAR(TO_DATE(T1.DATE, 'YYYYMMDD') - 1 DAY, 'YYYYMMDD') AS DATE
    FROM RECURSIVE_DATE T1
   WHERE T1.NUM < 10 /* 종료조건 */
)
SELECT T1.NUM
     , T1.DATE
  FROM RECURSIVE_DATE T1
 
FETCH FETCH FIRST 5 ROWS ONLY /* 5개만 출력 */