Udemy - The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert를 수강하며 정리하는 글
MIN/MAX With GROUP BY
Q. Find the year each author published their first book
mysql> SELECT author_fname, author_lname, MIN(released_year)
FROM books GROUP BY author_fname, author_lname;
+--------------+----------------+--------------------+
| author_fname | author_lname | MIN(released_year) |
+--------------+----------------+--------------------+
| Jhumpa | Lahiri | 1996 |
| Neil | Gaiman | 2003 |
| Dave | Eggers | 2001 |
| Michael | Chabon | 2000 |
| Patti | Smith | 2010 |
| Raymond | Carver | 1981 |
| Don | DeLillo | 1985 |
| John | Steinbeck | 1945 |
| David | Foster Wallace | 2004 |
| Dan | Harris | 2014 |
| Freida | Harris | 2001 |
| George | Saunders | 2017 |
+--------------+----------------+--------------------+
12 rows in set (0.00 sec)
년도별로 정렬하고 싶으면
mysql> SELECT author_fname, author_lname, MIN(released_year)
FROM books GROUP BY author_fname, author_lname ORDER BY 3;
+--------------+----------------+--------------------+
| author_fname | author_lname | MIN(released_year) |
+--------------+----------------+--------------------+
| John | Steinbeck | 1945 |
| Raymond | Carver | 1981 |
| Don | DeLillo | 1985 |
| Jhumpa | Lahiri | 1996 |
| Michael | Chabon | 2000 |
| Dave | Eggers | 2001 |
| Freida | Harris | 2001 |
| Neil | Gaiman | 2003 |
| David | Foster Wallace | 2004 |
| Patti | Smith | 2010 |
| Dan | Harris | 2014 |
| George | Saunders | 2017 |
+--------------+----------------+--------------------+
12 rows in set (0.00 sec)
mysql> SELECT author_fname, author_lname, MIN(released_year)
FROM books GROUP BY author_fname, author_lname ORDER BY MIN(released_year);
+--------------+----------------+--------------------+
| author_fname | author_lname | MIN(released_year) |
+--------------+----------------+--------------------+
| John | Steinbeck | 1945 |
| Raymond | Carver | 1981 |
| Don | DeLillo | 1985 |
| Jhumpa | Lahiri | 1996 |
| Michael | Chabon | 2000 |
| Dave | Eggers | 2001 |
| Freida | Harris | 2001 |
| Neil | Gaiman | 2003 |
| David | Foster Wallace | 2004 |
| Patti | Smith | 2010 |
| Dan | Harris | 2014 |
| George | Saunders | 2017 |
+--------------+----------------+--------------------+
12 rows in set (0.00 sec)
이렇게.
Q. Find the longest page count for each author
mysql> SELECT author_fname, author_lname, MAX(pages)
FROM books GROUP BY author_fname, author_lname;
+--------------+----------------+------------+
| author_fname | author_lname | MAX(pages) |
+--------------+----------------+------------+
| Jhumpa | Lahiri | 291 |
| Neil | Gaiman | 304 |
| Dave | Eggers | 504 |
| Michael | Chabon | 634 |
| Patti | Smith | 304 |
| Raymond | Carver | 526 |
| Don | DeLillo | 320 |
| John | Steinbeck | 181 |
| David | Foster Wallace | 343 |
| Dan | Harris | 256 |
| Freida | Harris | 428 |
| George | Saunders | 367 |
+--------------+----------------+------------+
12 rows in set (0.00 sec)
순서를 넣어주면(페이지 순으로),
mysql> SELECT author_fname, author_lname, MAX(pages)
FROM books GROUP BY author_fname, author_lname ORDER BY 3;
+--------------+----------------+------------+
| author_fname | author_lname | MAX(pages) |
+--------------+----------------+------------+
| John | Steinbeck | 181 |
| Dan | Harris | 256 |
| Jhumpa | Lahiri | 291 |
| Neil | Gaiman | 304 |
| Patti | Smith | 304 |
| Don | DeLillo | 320 |
| David | Foster Wallace | 343 |
| George | Saunders | 367 |
| Freida | Harris | 428 |
| Dave | Eggers | 504 |
| Raymond | Carver | 526 |
| Michael | Chabon | 634 |
+--------------+----------------+------------+
12 rows in set (0.00 sec)
mysql> SELECT author_fname, author_lname, MAX(pages)
FROM books GROUP BY author_fname, author_lname ORDER BY MAX(pages);
+--------------+----------------+------------+
| author_fname | author_lname | MAX(pages) |
+--------------+----------------+------------+
| John | Steinbeck | 181 |
| Dan | Harris | 256 |
| Jhumpa | Lahiri | 291 |
| Neil | Gaiman | 304 |
| Patti | Smith | 304 |
| Don | DeLillo | 320 |
| David | Foster Wallace | 343 |
| George | Saunders | 367 |
| Freida | Harris | 428 |
| Dave | Eggers | 504 |
| Raymond | Carver | 526 |
| Michael | Chabon | 634 |
+--------------+----------------+------------+
12 rows in set (0.01 sec)
- 좀 더 깔끔하게 정리
mysql> SELECT
CONCAT(author_fname, ' ', author_lname) AS author,
MAX(pages) AS 'longest book'
FROM books
GROUP BY author_lname, author_fname
ORDER BY MAX(pages);
+----------------------+--------------+
| author | longest book |
+----------------------+--------------+
| John Steinbeck | 181 |
| Dan Harris | 256 |
| Jhumpa Lahiri | 291 |
| Neil Gaiman | 304 |
| Patti Smith | 304 |
| Don DeLillo | 320 |
| David Foster Wallace | 343 |
| George Saunders | 367 |
| Freida Harris | 428 |
| Dave Eggers | 504 |
| Raymond Carver | 526 |
| Michael Chabon | 634 |
+----------------------+--------------+
12 rows in set (0.00 sec)
임재현입니다.