Section 02 Sorting Data/03 MySQL ORDER BY.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "dde687f9-77f6-4857-947d-b15cc53a3e9e",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[MySQL ORDER BY](https://www.mysqltutorial.org/mysql-order-by/)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b81a0b70-c8b4-473f-9a3e-49b2b6a7fa54",
   "metadata": {},
   "source": [
    "Sort the rows in a result set using the MySQL ORDER BY clause."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "fdd0ad8a-461b-426d-8cc5-b03dafc46ae8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.image as mpimg\n",
    "from IPython.display import Image, SVG\n",
    "\n",
    "from sqlalchemy_utils import database_exists, create_database\n",
    "from sqlalchemy import create_engine, inspect, MetaData, text\n",
    "from sqlalchemy_schemadisplay import create_schema_graph\n",
    "import pymysql\n",
    "\n",
    "pd.set_option(\n",
    "    'display.max_columns', None,\n",
    "    'expand_frame_repr', True,\n",
    "    # 'max_rows', 10, \n",
    "    'display.max_colwidth', None,\n",
    "    'display.max_rows', 10,\n",
    "    # 'precision', 2,\n",
    "    # 'width', 45\n",
    ")\n",
    "\n",
    "pd.set_option('display.width', 65)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "51c7e0b8-cb9d-4b1b-a3ab-961b40533185",
   "metadata": {},
   "outputs": [],
   "source": [
    "connect_args={'ssl':{'fake_flag_to_enable_tls': True}}\n",
    "\n",
    "engine = create_engine('mysql+pymysql://namlq:abc123@localhost/classicmodels',\n",
    "                       connect_args=connect_args, echo=False\n",
    "                         )\n",
    "inspector = inspect(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27d8b563-ad8d-42d7-aecb-3e3df03320e7",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Introduction to the MySQL ORDER BY clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0bba5348-45b3-478c-aa31-7e374868fe7e",
   "metadata": {},
   "source": [
    "syntax:"
   ]
  },
  {
   "cell_type": "raw",
   "id": "86d81981-9e2e-4811-972f-a850a6b23352",
   "metadata": {},
   "source": [
    "SELECT \n",
    "   select_list\n",
    "FROM \n",
    "   table_name\n",
    "ORDER BY \n",
    "   column1 [ASC|DESC], \n",
    "   column2 [ASC|DESC],\n",
    "   ...;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3bb3015c-9784-4264-8929-0c2db6e3e798",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL ORDER BY examples"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe0c1efa-6c60-4bec-aa5a-26d428e5952c",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### A) Sort the result set by one column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6b91a56f-ce6d-4ecb-9a5c-f4d0e9d7c7a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    contactLastName,\n",
    "    contactFirstName\n",
    "FROM\n",
    "    customers\n",
    "ORDER BY\n",
    "    contactLastname;\n",
    "'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a9967cac-42fe-42e2-88df-1c25bf3e3960",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (pd\n",
    " .read_sql_table('customers', engine,\n",
    "                 columns=['contactLastName', 'contactFirstName'])\n",
    " .sort_values(by='contactLastName',\n",
    "              kind='mergesort', # {'quicksort', 'mergesort', 'heapsort', 'stable'}\n",
    "              ignore_index=True,\n",
    "              # pandas is CASE-SENSITIVE, sql is CASE-insensitive\n",
    "              key=lambda col: col.str.upper())\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "adaaeb4d-7592-4dec-84da-a1da95721f12",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "38378ad1-63c7-4575-ba19-2f4d88e94be2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>contactLastName</th>\n",
       "      <th>contactFirstName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Accorti</td>\n",
       "      <td>Paolo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Altagar,G M</td>\n",
       "      <td>Raanan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andersen</td>\n",
       "      <td>Mel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Anton</td>\n",
       "      <td>Carmen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ashworth</td>\n",
       "      <td>Rachel</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  contactLastName contactFirstName\n",
       "0         Accorti           Paolo \n",
       "1     Altagar,G M           Raanan\n",
       "2        Andersen              Mel\n",
       "3           Anton           Carmen\n",
       "4        Ashworth           Rachel"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a09c880a-54b8-4383-a493-45913198bd1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    contactLastName,\n",
    "    contactFirstName\n",
    "FROM\n",
    "    customers\n",
    "ORDER BY\n",
    "    contactLastName DESC; \n",
    "'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "98bb1487-5583-40ae-848e-7d94cd08813c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (pd\n",
    " .read_sql_table('customers', engine,\n",
    "                 columns=['contactLastName', 'contactFirstName']\n",
    "                )\n",
    " .sort_values(by='contactLastName', ascending=False,\n",
    "              kind='mergesort', \n",
    "              ignore_index=True,\n",
    "              key=lambda col: col.str.upper())\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "0f01b8c8-9b41-42fd-b9be-eee7caa9d2b6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "3eac2750-462a-4362-8eea-44c819261d49",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>contactLastName</th>\n",
       "      <th>contactFirstName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Young</td>\n",
       "      <td>Jeff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Young</td>\n",
       "      <td>Julie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Young</td>\n",
       "      <td>Mary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Young</td>\n",
       "      <td>Dorothy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yoshido</td>\n",
       "      <td>Juri</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  contactLastName contactFirstName\n",
       "0           Young             Jeff\n",
       "1           Young            Julie\n",
       "2           Young             Mary\n",
       "3           Young          Dorothy\n",
       "4         Yoshido             Juri"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8855dd0e-b06f-4f03-b2ce-1a6753309b63",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### B) Sort the result set by multiple columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ffca1c75-ad02-4e02-809b-96fd3acd69d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    contactLastName,\n",
    "    contactFirstName\n",
    "FROM\n",
    "    customers\n",
    "ORDER BY\n",
    "    contactLastName DESC,\n",
    "    contactFirstName ASC;\n",
    "'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e4fae17b-f431-4766-8bd9-3772b3e98fd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (pd\n",
    " .read_sql_table('customers', engine,\n",
    "                 columns=['contactLastName', 'contactFirstName'])\n",
    " .sort_values(by=['contactLastName', 'contactFirstName'],\n",
    "              ascending=[False, True],\n",
    "              # kind='heapsort', \n",
    "              ignore_index=True,\n",
    "              key = lambda col: col.str.upper())\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "ace8299f-500a-4c65-a8c4-a1b45a1e5edb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "bbfcbad0-2894-4fd5-9c8c-025e4924006c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>contactLastName</th>\n",
       "      <th>contactFirstName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Young</td>\n",
       "      <td>Dorothy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Young</td>\n",
       "      <td>Jeff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Young</td>\n",
       "      <td>Julie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Young</td>\n",
       "      <td>Mary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yoshido</td>\n",
       "      <td>Juri</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  contactLastName contactFirstName\n",
       "0           Young          Dorothy\n",
       "1           Young             Jeff\n",
       "2           Young            Julie\n",
       "3           Young             Mary\n",
       "4         Yoshido             Juri"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5287611f-cf78-431d-93cd-583f871f1061",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### C) Sort the result set by an expression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "fb2c48bd-4def-4e13-8921-59aed6707559",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    orderNumber,\n",
    "    orderLineNumber,\n",
    "    quantityOrdered * priceEach AS subtotal\n",
    "FROM\n",
    "    orderdetails\n",
    "ORDER BY\n",
    "    orderNumber, subtotal DESC;\n",
    "'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "a67522b4-b749-41f7-b727-70df4665097a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (pd\n",
    " .read_sql_table('orderdetails', engine)\n",
    " .assign(subtotal = lambda df_: df_.quantityOrdered * df_.priceEach)\n",
    " [['orderNumber', 'orderLineNumber', 'subtotal']]\n",
    " .sort_values(by=['orderNumber', 'subtotal'], \n",
    "              kind='quicksort', # 'quicksort', 'mergesort', 'heapsort', 'stable'\n",
    "              ascending=[True, False],\n",
    "              ignore_index=True)\n",
    "      )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "016775c4-7701-4aa1-b61d-329297432490",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "4f92b250-02a3-4051-a2d1-7a9cbfdcc441",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "orderNumber          0\n",
       "orderLineNumber      0\n",
       "subtotal           745\n",
       "dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1 != df2).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "8ab8c674-b0db-41ca-86ea-9f498f3d7ed1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3.6004621506435797e-10"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1.subtotal - df2.subtotal).abs().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "8f8bc0bb-6baf-4505-90e9-0e2f0eaf747e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>orderNumber</th>\n",
       "      <th>orderLineNumber</th>\n",
       "      <th>subtotal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10100</td>\n",
       "      <td>3</td>\n",
       "      <td>4080.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10100</td>\n",
       "      <td>2</td>\n",
       "      <td>2754.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10100</td>\n",
       "      <td>1</td>\n",
       "      <td>1729.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10100</td>\n",
       "      <td>4</td>\n",
       "      <td>1660.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10101</td>\n",
       "      <td>1</td>\n",
       "      <td>4343.56</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   orderNumber  orderLineNumber  subtotal\n",
       "0        10100                3   4080.00\n",
       "1        10100                2   2754.50\n",
       "2        10100                1   1729.21\n",
       "3        10100                4   1660.12\n",
       "4        10101                1   4343.56"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0e2b6fe-914d-4bca-aae9-823a7eafcd3d",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### D) Sort data using a custom list"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d8435d5-02d8-4186-b39e-b3f38f9a1937",
   "metadata": {},
   "source": [
    "syntax of `FIELD()` function:"
   ]
  },
  {
   "cell_type": "raw",
   "id": "9a58a07b-7b83-437b-b2c4-0bacb6f72e5a",
   "metadata": {},
   "source": [
    "FIELD(str, str1, str2, ...)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "d8e6ce3c-2c93-4efa-8dc2-b594e62f6e56",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>position</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   position\n",
       "0         1"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\"SELECT FIELD ('A', 'A', 'B', 'C') as position\", engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "1745fb17-d581-4e8a-89e6-f7f58a86602e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>position</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   position\n",
       "0         2"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\"SELECT FIELD ('B', 'A', 'B', 'C') as position\", engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "2f83bffd-bb9d-453c-914f-5d5966bb75ea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>status</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Shipped</td>\n",
       "      <td>303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Resolved</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Cancelled</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>On Hold</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Disputed</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>In Process</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       status  count\n",
       "0     Shipped    303\n",
       "1    Resolved      4\n",
       "2   Cancelled      6\n",
       "3     On Hold      4\n",
       "4    Disputed      3\n",
       "5  In Process      6"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql('SELECT status, COUNT(*) count FROM orders GROUP BY status', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "833bbc5a-a688-4053-8377-3ad60752c840",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    orderNumber, status\n",
    "FROM\n",
    "    orders\n",
    "ORDER BY FIELD(status,\n",
    "        'In Process',\n",
    "        'On Hold',\n",
    "        'Cancelled',\n",
    "        'Resolved',\n",
    "        'Disputed',\n",
    "        'Shipped');\n",
    "'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "731a0233-ef67-4c06-ac27-390f4b4b6946",
   "metadata": {},
   "outputs": [],
   "source": [
    "categories = ['In Process', 'On Hold', 'Cancelled', 'Resolved', 'Disputed', 'Shipped']\n",
    "df2 = (pd\n",
    " .read_sql_table('orders', engine)\n",
    " [['orderNumber', 'status']]\n",
    " .sort_values(\n",
    "     by='status',\n",
    "     kind='mergesoft',\n",
    "     key=lambda col: pd.Categorical(col, categories=categories, ordered=True), \n",
    "     ignore_index=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "28932e32-ae23-4589-b5c5-8a9e731ccc0b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "395a65e9-17e6-4340-8c63-a9f7be4bb9bb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>orderNumber</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10420</td>\n",
       "      <td>In Process</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10421</td>\n",
       "      <td>In Process</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10422</td>\n",
       "      <td>In Process</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10423</td>\n",
       "      <td>In Process</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10424</td>\n",
       "      <td>In Process</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   orderNumber      status\n",
       "0        10420  In Process\n",
       "1        10421  In Process\n",
       "2        10422  In Process\n",
       "3        10423  In Process\n",
       "4        10424  In Process"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09352a6d-1e73-41d1-9e1d-3ba5934401c5",
   "metadata": {
    "tags": []
   },
   "source": [
    "### ORDER BY and NULL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "309061ca-f40b-43e7-96d5-1002abc9107a",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT \n",
    "    firstName, lastName, reportsTo\n",
    "FROM employees\n",
    "ORDER BY reportsTo, lastName, firstName\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "e8bd979b-5a25-460d-9b03-f619ecb4c31f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (pd\n",
    " .read_sql_table('employees', engine)\n",
    " [['firstName', 'lastName', 'reportsTo']]\n",
    " .sort_values(\n",
    "     by=['reportsTo', 'lastName', 'firstName'],\n",
    "     ascending=[True, True, True],\n",
    "     na_position='first',\n",
    "     ignore_index=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "420d66fa-38d5-4256-817e-a70237a8a527",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "6e8b4f9d-9d2e-4408-9e20-749d07a6ccf0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>firstName</th>\n",
       "      <th>lastName</th>\n",
       "      <th>reportsTo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Diane</td>\n",
       "      <td>Murphy</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jeff</td>\n",
       "      <td>Firrelli</td>\n",
       "      <td>1002.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>Patterson</td>\n",
       "      <td>1002.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Gerard</td>\n",
       "      <td>Bondur</td>\n",
       "      <td>1056.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Anthony</td>\n",
       "      <td>Bow</td>\n",
       "      <td>1056.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  firstName   lastName  reportsTo\n",
       "0     Diane     Murphy        NaN\n",
       "1      Jeff   Firrelli     1002.0\n",
       "2      Mary  Patterson     1002.0\n",
       "3    Gerard     Bondur     1056.0\n",
       "4   Anthony        Bow     1056.0"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "938b0ab5-e222-4da4-b257-dc1e6c5a9bb9",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    firstName, lastName, reportsTo\n",
    "FROM\n",
    "    employees\n",
    "ORDER BY reportsTo DESC, lastName, firstName;\n",
    "'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "ea40cfc0-c7fc-48c0-9726-3f96091fc147",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (pd\n",
    " .read_sql_table('employees', engine)\n",
    " [['firstName', 'lastName', 'reportsTo']]\n",
    " .sort_values(\n",
    "     by=['reportsTo', 'lastName', 'firstName'],\n",
    "     ascending=[False, True, True],\n",
    "     na_position='last',\n",
    "     ignore_index=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "6f157984-6010-4b77-857c-ddb7472ab566",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "9c4fb9d4-1d0b-4f6c-8d41-05bdb6072b94",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>firstName</th>\n",
       "      <th>lastName</th>\n",
       "      <th>reportsTo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Mami</td>\n",
       "      <td>Nishi</td>\n",
       "      <td>1056.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>William</td>\n",
       "      <td>Patterson</td>\n",
       "      <td>1056.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Jeff</td>\n",
       "      <td>Firrelli</td>\n",
       "      <td>1002.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Mary</td>\n",
       "      <td>Patterson</td>\n",
       "      <td>1002.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Diane</td>\n",
       "      <td>Murphy</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   firstName   lastName  reportsTo\n",
       "18      Mami      Nishi     1056.0\n",
       "19   William  Patterson     1056.0\n",
       "20      Jeff   Firrelli     1002.0\n",
       "21      Mary  Patterson     1002.0\n",
       "22     Diane     Murphy        NaN"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.tail()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.15"
  },
  "toc-autonumbering": true,
  "toc-showmarkdowntxt": false,
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}