Udemy - The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert를 수강하며 정리하는 글
ORDER BY
sorting result
SELECT 칼럼명
FROM 테이블
ORDER BY 순서를 메기고 싶은 칼럼명
mysql> SELECT author_lname FROM books ORDER BY author_lname;
+----------------+
| author_lname |
+----------------+
| Carver |
| Carver |
| Chabon |
| DeLillo |
| Eggers |
| Eggers |
| Eggers |
| Foster Wallace |
| Foster Wallace |
| Gaiman |
| Gaiman |
| Gaiman |
| Harris |
| Harris |
| Lahiri |
| Lahiri |
| Saunders |
| Smith |
| Steinbeck |
+----------------+
19 rows in set (0.00 sec)알파벳 순으로 정렬되어서 나오는 것을 볼 수 있다.(오름차순. Ascending) Default가 오름차순이다.(ASC를 붙여줘도 되지만 안붙여줘도 Default가 ASC이다.) 내림차순으로 하고 싶으면 뒤에 DESC(Descending)을 붙이면 된다.
mysql> SELECT author_lname FROM books ORDER BY author_lname DESC;
+----------------+
| author_lname |
+----------------+
| Steinbeck |
| Smith |
| Saunders |
| Lahiri |
| Lahiri |
| Harris |
| Harris |
| Gaiman |
| Gaiman |
| Gaiman |
| Foster Wallace |
| Foster Wallace |
| Eggers |
| Eggers |
| Eggers |
| DeLillo |
| Chabon |
| Carver |
| Carver |
+----------------+
19 rows in set (0.00 sec)이렇게 내림차순으로 정렬되서 나오는 것을 볼 수 있다.
이번엔 title을 출력해보자. 먼저 ORDER BY없이.
mysql> SELECT title FROM books;
+-----------------------------------------------------+
| title |
+-----------------------------------------------------+
| The Namesake |
| Norse Mythology |
| American Gods |
| Interpreter of Maladies |
| A Hologram for the King: A Novel |
| The Circle |
| The Amazing Adventures of Kavalier & Clay |
| Just Kids |
| A Heartbreaking Work of Staggering Genius |
| Coraline |
| What We Talk About When We Talk About Love: Stories |
| Where I'm Calling From: Selected Stories |
| White Noise |
| Cannery Row |
| Oblivion: Stories |
| Consider the Lobster |
| 10% Happier |
| fake_book |
| Lincoln In The Bardo |
+-----------------------------------------------------+
19 rows in set (0.00 sec)이번에는 ORDER BY로 순서대로 출력해보자.
mysql> SELECT title FROM books ORDER BY title;
+-----------------------------------------------------+
| title |
+-----------------------------------------------------+
| 10% Happier |
| A Heartbreaking Work of Staggering Genius |
| A Hologram for the King: A Novel |
| American Gods |
| Cannery Row |
| Consider the Lobster |
| Coraline |
| fake_book |
| Interpreter of Maladies |
| Just Kids |
| Lincoln In The Bardo |
| Norse Mythology |
| Oblivion: Stories |
| The Amazing Adventures of Kavalier & Clay |
| The Circle |
| The Namesake |
| What We Talk About When We Talk About Love: Stories |
| Where I'm Calling From: Selected Stories |
| White Noise |
+-----------------------------------------------------+
19 rows in set (0.00 sec)
-- 내림차순으로
mysql> SELECT title FROM books ORDER BY title DESC;
+-----------------------------------------------------+
| title |
+-----------------------------------------------------+
| White Noise |
| Where I'm Calling From: Selected Stories |
| What We Talk About When We Talk About Love: Stories |
| The Namesake |
| The Circle |
| The Amazing Adventures of Kavalier & Clay |
| Oblivion: Stories |
| Norse Mythology |
| Lincoln In The Bardo |
| Just Kids |
| Interpreter of Maladies |
| fake_book |
| Coraline |
| Consider the Lobster |
| Cannery Row |
| American Gods |
| A Hologram for the King: A Novel |
| A Heartbreaking Work of Staggering Genius |
| 10% Happier |
+-----------------------------------------------------+
19 rows in set (0.00 sec)이번에는 숫자에 적용해보자. 먼저 ORDER BY없이.
mysql> SELECT released_year FROM books;
+---------------+
| released_year |
+---------------+
| 2003 |
| 2016 |
| 2001 |
| 1996 |
| 2012 |
| 2013 |
| 2000 |
| 2010 |
| 2001 |
| 2003 |
| 1981 |
| 1989 |
| 1985 |
| 1945 |
| 2004 |
| 2005 |
| 2014 |
| 2001 |
| 2017 |
+---------------+
19 rows in set (0.00 sec)이번에는 ORDER BY를 붙여보자.
mysql> SELECT released_year FROM books ORDER BY released_year;
+---------------+
| released_year |
+---------------+
| 1945 |
| 1981 |
| 1985 |
| 1989 |
| 1996 |
| 2000 |
| 2001 |
| 2001 |
| 2001 |
| 2003 |
| 2003 |
| 2004 |
| 2005 |
| 2010 |
| 2012 |
| 2013 |
| 2014 |
| 2016 |
| 2017 |
+---------------+
19 rows in set (0.00 sec)
mysql> SELECT released_year FROM books ORDER BY released_year DESC;
+---------------+
| released_year |
+---------------+
| 2017 |
| 2016 |
| 2014 |
| 2013 |
| 2012 |
| 2010 |
| 2005 |
| 2004 |
| 2003 |
| 2003 |
| 2001 |
| 2001 |
| 2001 |
| 2000 |
| 1996 |
| 1989 |
| 1985 |
| 1981 |
| 1945 |
+---------------+
19 rows in set (0.00 sec)이번에는 다른 칼럼들과 결합하여 데이터를 출력해보자.
mysql> SELECT title, pages, released_year FROM books ORDER BY released_year;
+-----------------------------------------------------+-------+---------------+
| title | pages | released_year |
+-----------------------------------------------------+-------+---------------+
| Cannery Row | 181 | 1945 |
| What We Talk About When We Talk About Love: Stories | 176 | 1981 |
| White Noise | 320 | 1985 |
| Where I'm Calling From: Selected Stories | 526 | 1989 |
| Interpreter of Maladies | 198 | 1996 |
| The Amazing Adventures of Kavalier & Clay | 634 | 2000 |
| American Gods | 465 | 2001 |
| A Heartbreaking Work of Staggering Genius | 437 | 2001 |
| fake_book | 428 | 2001 |
| The Namesake | 291 | 2003 |
| Coraline | 208 | 2003 |
| Oblivion: Stories | 329 | 2004 |
| Consider the Lobster | 343 | 2005 |
| Just Kids | 304 | 2010 |
| A Hologram for the King: A Novel | 352 | 2012 |
| The Circle | 504 | 2013 |
| 10% Happier | 256 | 2014 |
| Norse Mythology | 304 | 2016 |
| Lincoln In The Bardo | 367 | 2017 |
+-----------------------------------------------------+-------+---------------+
19 rows in set (0.00 sec)출판년도별로 데이터가 나왔다. 여기서 released_year는 출력을 안하고, 순서정렬용으로만 사용해줄 수도있다.(SELECT 칼럼명에는 안쓰고 ORDER BY뒤에만 써주면 된다.)
mysql> SELECT title, pages FROM books ORDER BY released_year;
+-----------------------------------------------------+-------+
| title | pages |
+-----------------------------------------------------+-------+
| Cannery Row | 181 |
| What We Talk About When We Talk About Love: Stories | 176 |
| White Noise | 320 |
| Where I'm Calling From: Selected Stories | 526 |
| Interpreter of Maladies | 198 |
| The Amazing Adventures of Kavalier & Clay | 634 |
| American Gods | 465 |
| A Heartbreaking Work of Staggering Genius | 437 |
| fake_book | 428 |
| The Namesake | 291 |
| Coraline | 208 |
| Oblivion: Stories | 329 |
| Consider the Lobster | 343 |
| Just Kids | 304 |
| A Hologram for the King: A Novel | 352 |
| The Circle | 504 |
| 10% Happier | 256 |
| Norse Mythology | 304 |
| Lincoln In The Bardo | 367 |
+-----------------------------------------------------+-------+
19 rows in set (0.00 sec)아까랑 같은 순서로 나왔다.
SELECT title, author_fname, author_lname FROM books ORDER BY 2;
이번에는 위 SQL문을 봐보자. ORDER BY 2가 무슨 말일까?
먼저 실행해보겠다.
mysql> SELECT title, author_fname, author_lname FROM books ORDER BY 2;
+-----------------------------------------------------+--------------+----------------+
| title | author_fname | author_lname |
+-----------------------------------------------------+--------------+----------------+
| 10% Happier | Dan | Harris |
| A Hologram for the King: A Novel | Dave | Eggers |
| The Circle | Dave | Eggers |
| A Heartbreaking Work of Staggering Genius | Dave | Eggers |
| Oblivion: Stories | David | Foster Wallace |
| Consider the Lobster | David | Foster Wallace |
| White Noise | Don | DeLillo |
| fake_book | Freida | Harris |
| Lincoln In The Bardo | George | Saunders |
| The Namesake | Jhumpa | Lahiri |
| Interpreter of Maladies | Jhumpa | Lahiri |
| Cannery Row | John | Steinbeck |
| The Amazing Adventures of Kavalier & Clay | Michael | Chabon |
| Norse Mythology | Neil | Gaiman |
| American Gods | Neil | Gaiman |
| Coraline | Neil | Gaiman |
| Just Kids | Patti | Smith |
| What We Talk About When We Talk About Love: Stories | Raymond | Carver |
| Where I'm Calling From: Selected Stories | Raymond | Carver |
+-----------------------------------------------------+--------------+----------------+
19 rows in set (0.00 sec)여기서 눈치 챘을 수 도 있겠지만, 정답은 2번째 껄로 ORDER BY한다는 의미이다.
이번에는 3번째 칼럼으로(여기서는 author_lname으로 ORDER BY해보자)
mysql> SELECT title, author_fname, author_lname FROM books ORDER BY 3;
+-----------------------------------------------------+--------------+----------------+
| title | author_fname | author_lname |
+-----------------------------------------------------+--------------+----------------+
| What We Talk About When We Talk About Love: Stories | Raymond | Carver |
| Where I'm Calling From: Selected Stories | Raymond | Carver |
| The Amazing Adventures of Kavalier & Clay | Michael | Chabon |
| White Noise | Don | DeLillo |
| A Hologram for the King: A Novel | Dave | Eggers |
| The Circle | Dave | Eggers |
| A Heartbreaking Work of Staggering Genius | Dave | Eggers |
| Oblivion: Stories | David | Foster Wallace |
| Consider the Lobster | David | Foster Wallace |
| Norse Mythology | Neil | Gaiman |
| American Gods | Neil | Gaiman |
| Coraline | Neil | Gaiman |
| 10% Happier | Dan | Harris |
| fake_book | Freida | Harris |
| The Namesake | Jhumpa | Lahiri |
| Interpreter of Maladies | Jhumpa | Lahiri |
| Lincoln In The Bardo | George | Saunders |
| Just Kids | Patti | Smith |
| Cannery Row | John | Steinbeck |
+-----------------------------------------------------+--------------+----------------+
19 rows in set (0.00 sec)이번에는
mysql> SELECT author_fname, author_lname FROM books ORDER BY author_lname, author_fname;
이렇게 ORDER BY뒤에 칼럼 두개를 입력해주면 어떻게 될까?
먼저 author_lname으로 정렬하고, 그 뒤에 author_fname으로 정렬하라는 말이다.
mysql> SELECT author_fname, author_lname FROM books ORDER BY author_lname, author_fname;
+--------------+----------------+
| author_fname | author_lname |
+--------------+----------------+
| Raymond | Carver |
| Raymond | Carver |
| Michael | Chabon |
| Don | DeLillo |
| Dave | Eggers |
| Dave | Eggers |
| Dave | Eggers |
| David | Foster Wallace |
| David | Foster Wallace |
| Neil | Gaiman |
| Neil | Gaiman |
| Neil | Gaiman |
| Dan | Harris |
| Freida | Harris |
| Jhumpa | Lahiri |
| Jhumpa | Lahiri |
| George | Saunders |
| Patti | Smith |
| John | Steinbeck |
+--------------+----------------+
19 rows in set (0.01 sec)
위 데이터를 보면 먼저 author_lname으로 정렬된 뒤에 author_fname으로 정렬 된 것을 볼 수 있다. 무슨 얘기냐면,
Dan Harris와 Freid Harris부분을 보면 author_lname이 같다. 그래서 그 뒤에 author_fname순으로 정렬 되는 거다.
이번에는 author_lname은 내림차순으로, author_fname은 오름차순으로 정렬해보겠다.
mysql> SELECT author_fname, author_lname FROM books ORDER BY author_lname DESC, author_fname;
+--------------+----------------+
| author_fname | author_lname |
+--------------+----------------+
| John | Steinbeck |
| Patti | Smith |
| George | Saunders |
| Jhumpa | Lahiri |
| Jhumpa | Lahiri |
| Dan | Harris |
| Freida | Harris |
| Neil | Gaiman |
| Neil | Gaiman |
| Neil | Gaiman |
| David | Foster Wallace |
| David | Foster Wallace |
| Dave | Eggers |
| Dave | Eggers |
| Dave | Eggers |
| Don | DeLillo |
| Michael | Chabon |
| Raymond | Carver |
| Raymond | Carver |
+--------------+----------------+
19 rows in set (0.00 sec)위 데이터를 보면 author_lname이 내림차순으로 정렬되었지만 Dan Harris와 Freid Harris부분이 오름차순으로 정렬 된 것을 볼 수 있다.
임재현입니다.