Section 03 Filtering Data/12 MySQL LIMIT.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8b267632-4819-4b22-96aa-8e667f710ce2",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[MySQL LIMIT](https://www.mysqltutorial.org/mysql-limit.aspx)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e93d174e-929d-4837-b1fe-a6789add7d56",
   "metadata": {},
   "source": [
    "Use MySQL LIMIT clause to constrain the number of rows returned by a query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "01f64114-1e78-4f95-b546-9419a9c5c2f0",
   "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": "9527b746-bb83-496e-a8ab-71923febb465",
   "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": "2358c99c-5fe8-49fe-bde5-ec33a80d8e31",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Introduction to MySQL LIMIT clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f1ca1c7-295e-4f31-9548-c837dfc7f2ea",
   "metadata": {},
   "source": [
    "The `LIMIT` clause is used in the `SELECT` statment to constrain the number of rows to return.\n",
    "\n",
    "Syntax: **`LIMIT [offset,] row_count;`** or **`LIMIT row_count OFFSET offset`**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "efbdb822-e911-466f-9a0a-cafc236aeb7d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAb0AAADwCAYAAACUn7pCAAAACXBIWXMAAA7EAAAOxAGVKw4bAAAAB3RJTUUH4wgfCjQxg225nwAAAAd0RVh0QXV0aG9yAKmuzEgAAAAMdEVYdERlc2NyaXB0aW9uABMJISMAAAAKdEVYdENvcHlyaWdodACsD8w6AAAADnRFWHRDcmVhdGlvbiB0aW1lADX3DwkAAAAJdEVYdFNvZnR3YXJlAF1w/zoAAAALdEVYdERpc2NsYWltZXIAt8C0jwAAAAh0RVh0V2FybmluZwDAG+aHAAAAB3RFWHRTb3VyY2UA9f+D6wAAAAh0RVh0Q29tbWVudAD2zJa/AAAABnRFWHRUaXRsZQCo7tInAAAgAElEQVR4nO3dcUxUZ7438O/c3pW72SvWsI0Fi9IZXmGLt7pi9doO+layDHkX825vaQNsuvVapYGkSW0MJK4Btb0mTExrbIJb5EW7bmSupbG70huGLm5hWNl6hbYuVks5UyoKba1RoNtdyb133j/GM3POzAAzw8w858z5fpKJM2eGc34wx/nO85znPMfk8Xg8ICIiMoC/E10AERFRojD0iIjIMBh6RERkGAw9IiIyjL8XXQBRtD788EOYTCbRZVCCrF69WnQJlARMHL1JenT58mWsW7cOq1atEl0KJcC1a9ewefNmtLS0iC6FdI4tPdKtnJwc9Pb2ii6DEuDAgQOiS6AkwWN6RERkGAw9IiIyDIYeEREZBkOPiIgMg6FHRESGwdAjIiLDYOgREZFhMPSIdKS6uhomk4nnJxJFiSenE+kEp1wjmj+29Ih0oLi4GDabDS6XS3QpRLrGlh6RDnR0dAAAuzWJ5oktPSIiMgyGHhERGQZDj4iIDIOhR0REhsHQIyIiw2DoERGRYTD0iHSguLgYJpMJBQUFAICCggKYTCbY7XbBlRHpC8/TI9IB+Tw9IpoftvSIiMgwGHpERGQYDD0iIjIMhh4RERkGQ4+IiAyDoUdERIbBUxZIt7777jv09/eLLoMS4Pr161i6dKnoMigJMPRIt0ZHR/HSSy+JLoMS4OrVqygsLBRdBiUBhh7pVk5ODrq7u0WXQQlw4MAB0SVQkuAxPSIiMgyGHhERGQZDj4iIDIOhR0REhsHQIyIiw2DoERGRYTD0iIjIMBh6RDrgcDhgMpl8t+LiYtElEekSQ49I49xuN8rLy+HxeODxeCBJEpxOJ6qrq0WXRqQ7DD0ijTObzfB4PKrHVVVV6OzsFFgVkT4x9Ih0KCsrS3QJRLrE0CPSoaamJhQVFYkug0h3GHpEOuNwOCBJEioqKkSXQqQ7DD0iHZEHtTQ0NMBqtYouh0h3GHpEOmKxWGCz2VBTUyO6FCJdYugR6UR2djZsNhs6OjpEl0KkWww9Ih2QT0Zn4BHND0OPSON6e3vhdDohSZJqVhaTyQS73S66PCJd+XvRBRDR7KxWq+rkdCKKHlt6RERkGAw9IiIyDIYeEREZBkOPiIgMg6FHRESGwdAjIiLD4CkLpFt/+ctf8Kc//Ul0GZQAo6OjyMzMFF0GJQGGHunW9evX8ctf/lJ0GZQAIyMjKCwsFF0GJQGGHunWihUr0NXVJboMSoADBw6ILoGSBI/pERGRYTD0iIjIMBh6RERkGAw9IiIyDIYeEREZBkOPiIgMg6FHRESGwdAj0gGHw6G6Ynp2drbokoh0iaFHpAPl5eXweDy+myRJqK6uFl0Wke4w9Ih0wOPxqB5XVVWhs7NTUDVE+sXQI9Iht9vNLk6iKHDuTSKdcTgccDqdaG1tFV0Kke4w9Ih0oLe3FwUFBb7HDQ0NKCsrE1gRkT6xe5NIB6xWq2ogS1NTE7s3iaLA0CPSoc7OTkiSBIfDIboUIl1h6BERkWEw9Ig0zuFwoLi4WLWsuroaFouFx/WIIsTQI9I4OdiUM7IMDw9jeHhYcGXJK3AGnFATASifV956e3t9r7Hb7SFfE/glxu12B70mUHV1dch1ybWFWsd8ZvHp7e0NWofdbo9oHVrE0ZtEOtDR0SG6BMOw2+2ora2FJEkwm80AvAHndruD3oeGhgbU1NTMuc7AyQWU5JG5gevKzs4O+mJjsVhm/LJjNptV28nOzkZRUREaGxvnrC8UefCUzO12w2KxAEBYv7NWsaVHRHSX2+1GbW0tWltbfYEHAC6XC06nU9WKi5WTJ0/CYrEEBYnWWvJmsxk2mw1nz54VXcq8MPSIiO46f/48AAQdK7VarbBYLDh58mRctitJEtxud1zWHWvKLwN6xO5NSl4DA8C77wLDw8DYGDA+7v331i3RlRnb4sVARgaQnu79Nzsb+OlPgTVrRFeGnp4eXxdeoOzs7LgE065du3DkyBFYLJawu0tFkGcCkiRJdCnzwpYeJZezZ4GXXgJycoD8fKCuDvj1r4Hf/x64dImBpwW3bnnfi9//3vve1NV536ucHO97p6Pus9ra2jkHoADBg16UA0LkY3E2m823vpkGnUiSlNDBJcrBLOXl5WhoaNB9S4+hR8lhYAAoLQUKC4HXXgOGhkRXRJEaGvK+d4WF3vdyYEB0RXNqaGhQzZQz04CVwNeEas11dHTA4/GgtbXVF26BLUuLxRLWumIlGWcCYuiRvl2/Djz/vLel8Pbb6ucWLwaeeQY4fhzo7AT+/Gfgm28Aj4c3kbdvvvG+F52d3vfmmWe875XS229739Pnn/e+xwmSlZU1Y/fd8PAwNm/eHPcaysrKfDW0tbXFfXuROH78OCRJisuAnkThMT3SrR/duIE7+flI+eor1fIvS0rwzeOP4/a6deofuH3beyNt+P73AYvFe6usxL3/+Z/44dmzuL+93f+apibc+e1vsWDdOkz/8z/HvaTS0lLU1tbC4XCoBrP09vZCkiSUlpbGvQZA+4NFMjIyRJcQNYYe6dKi06dx4upV1bLe++7DyawsfPrdd94BLO++K6g6mo+cRx5BxcgIrDduAABSvvoKu86cwZtTU8Du3XHdttlsRlVVFcrLy1WhV1BQgKqqqriEUajz8eQTzhMVsuGK598hURh6pD/19cjYv9/30HPvvfhbUxNWFRdjlcCyKLb+2tGBf6ishOlu6/zZ998H6uuBffviul35ZG7loJR4jqocHh4OGgAjH7sLJB/rU6qqqor6BPTZyCfpK7lcLlit1phvK5FMntmmCiDSmuPHgX/9V9/D/169Gn87ehT/k5MjriaKm7/79FP8w44duOejj/wLjx0Dtm4VVhPpG0OP9KOnB9i0yffwy+99Dz8YGwNSUgQWRXF35w7+x2LBoslJ/7LubmDjRnE1kW5x9Cbpw/Xr3lF+d/0tLw//96GHGHhGkJKCwy+8gC+XLvUve+aZhI7qpOTB0CN92L8fkAeuLF6MsYYGTM9wIjAln/+65x787qmn/Kc2XL3q3SeIIsTQI+0bGACamvyPf/1r3NHx6DGKzjf33eedwUXW1KSLE9hJWxh6pH0HDvjvP/EEUFIirhYSq6TEuw/IlPsGURgYeqRtZ8+qZ1qJ83lapAPKfeDtt4E//EFcLaQ7DD3SNuXsHNu3A2vXiqtFI1atWoXU1FTNTVGVMGvXevcFmXIfCTA9PY2LFy/iypUrCSiM9IChR9qmnFXl6afF1aERhw4d0vVsGDHz1FP++yFCTw673/3udxgcHMT09HQCiyMt44wspF0DA/6rJSxeDPzkJ2LrEWxkZAR1dXW4ePEiHn74YdHliFVU5N0nbt3y7iMffgj8+MeYnp7GlStXMDQ0xKCjkBh6pF3KVh4Hr2Dnzp3Yvn07srKyRJeiDSUlwIkTAID//u1vcemee2YMu8HBQQwODia6QkqglStXhvVlkKFH2qWchLewUFwdGtDW1oauri5MKmclMbrCQl/oXXv/fQyuWCG4INIDHtMj7Rob89/X8aVMYuHll19GS0uL6DK0RbFPLP/e97BmzRqBxZBesKVH2jU+7r+fni6uDsHkwStau8yMcMp9Ynwcubm5yM3NxZUrVzAQcNJ6uF1flPzY0iPtUrb0DBx63d3d6OrqQmpqqu8GANu2bcOqVQa+mJJyn1DsK7m5uaioqGDLj0JiS4+069Yt//20NHF1CHb69OmgZampqWhpaTF260+5Tyj3lbuULT+O5CQZQ4+Iklpubq7oEkhD2L1JRESGwZYekQ7x1AWi6LClR0REhsHQIyIiw2DoERGRYTD0iIjIMCIayOJyT2Dj6x+plnle26R6vKV5EO2Xbgb9bEleGs5sX+l7bNrZjR0b0tH09Mzz5a19dQD9o1NBy0P9XOWpIRzt88/godxeRn0fxidnPk+n54XVKDAvmvH5eDLt7A65/P+V5WDb+vsBhP47zPS3C/xdlb9bywdf4jnHp0Hvhbx+5TbD5XA4UF5eDo/HE9HPERGJEHboyR+YgR+2GfV9GNu3QfXa9NQFQcuilZ+5EBdemn1mBflDWxnA+5xfYJ/zC9Tblqtq2dI8iP7RqZjVFwtzhT+g/jvI7wUA1c+ZdnarAq3y1BA2vv6RL8xGb98BANWXEpd7AmMTd2L6+xARaVXYoXf6z98gPXVB0Iez6PCoPDWE/tEp9LywWrW83rZcUEXxt239/Wj84xgGrn3rW7aleRDpqQtULbimp1dg4Nq3aPzjmK8Fl566ABmLUnxfCE5c+AoleWk42jfuC0W9+Pbbb+FyuUSXQQkwMjKCnJwc0WVQEoioe3N8chou94SwrsBQ2i/dRH7mwoTV1Nvbi4KCAkiShKKiIkiSBACoqqpCY2NjQmoIpX90CiV5wVN1bclLw96OEbjcEwCAjEUp2JKXhgt3u0vbL93Evz/7UMgu6dkUFxfD6XT6HptMJt99m82Gjo6OaH6NiHz99dc4fPhw3LdD4rndbvzgBz8QXQYlgbBDr2ZzJtov3cTG1z8KqzsuUcYnp0N+2MebxWJBa2srysrKfEFYUVEBq9Ua923vc36B/tEp7C3OAuDtohyfnMbSRSlBr82817vssxt/xfW73Zj1tuUw7eyGyz2BjEUpUX1hkENN5DE9s9mMt956K+HbpcQ7ePAgUlKC92+iSIUdegXmRfC8tglrXx3A0b5xHO0bn/HY3fjkdNAAjWiDsn90Kmhde4uzUG9b7mu9iCAHHgBf0J07dy6q0JP/nkqBA4QC/w7zHXxTkpeGne9I2KL4wnCdx/aIKMlFPA2ZPJhin/ML7O0YgWlnd9AHcKIHsojwwAMPxGxdkQxkkQexnLjwVcShpxzVuTZzIfZ2jGjyb0tEFC9Rn6dXb1vuGzxy4sJXMSsoEgXmRUhPXTDr6QjJZtv6+30DT+SWrhx+oVpq8uCUwFMR6m3Lg1qTRETJbl4np2thQEt+5sKIB2HoXc3mTADqLxsleWkh/w5nLt1UHfNMT10Qcp0Zi1IM9eWBiIwp7NDLqO8LWraleRAA8MzaJbGrKELyEP3A437yeXrJqMC8CPmZC1XHAWs2Z2J8chqVp4Z8y+TTOeSQjEeoyd28drs95usmIoq1sI/pje3bEBQs6akLQnaRhRrIEjgLCBB6AIc8SEUWaiBL4DEweYCN8nWhtpdMqh/LwHOOT1F5aghNT69AgXkRel5YjY2vf6T6m0bShRlNKFqtVrS2tqK8vBy1tbUAEnfKAhFRpEwezh9FWqU49w8Bu+nly5dRUVGBnp6eBBdFIsinLOzevVv9xCz7CFEonHCaSAdWrVqF1NRU1e2JJ54QXRaR7vDK6UQ6sX//frz44ouiyyDSNbb0iIjIMBh6RERkGAw9Ip2oq6tTHdMjosjxmB6RDnz88ceqx/LAlsnJSUEVEekTW3pEOvSrX/0KANDW1ia4EiJ9YegR6VB6erroEoh0iaFHpEPj495Zd5YuXSq4EiJ9ifiYXuCUYIHTXGXU9wVNZxU4tVjgOoDQl9dZ++oA+u9e4Xum11aeGgqayizayxGF2p5yXfLllELV6nJPYOPrH2n2UkikX21tbTh37hxeffVV3zKbzYbCwkJs2BCbS3gRGUXYoSd/qCs/8Lc0D4a8np5y3ks5KACogk+5Hnnd7ZduBl2HL9wQUYavaWc31r46EFX4BG7PtLMbW5oHcWb7StTbluPC6BSO9o3jmbVLVL+z/ewoADDwKOZKS0uxbds2NDc3+5Zt375dFYJEFJ6wuzd3viMhP3OhqoVzZvtKpKcu8H3gh1JvW4701AW4EKLFJpMnSx6fnI7JlRF2bEgP2UKMxbrkKxYof2eXewLtl25ib3FWTLZJFGhyclJ1Y+ARRSfs0OsfncIWxXXZZDNdxy1S8gVhz2js2niBXbUF5kXYsSEd7Zdu+i7iuvMdCempC1Qt2XA4HA6Y7k6YazKZfDdepoeIKD7CCr2WD74EAGTemxL03NJF3mVyAASqPDWE8clpPPFPP5xzO/mZCzEW4urfkXC5J3C0b1x14dT5rKv90k08/2iGarnc2rWfHUXLB1+if3QKr/yfB6PejslkgsvlgsfjQWtrK2pra+F2u+dVOxERBYvLyentl26qBqtEck23QKGupxc4MAZQD44J9Xy028vPXBhyXXuLs7C3YwT9o1PIz1yIbevvj2p7AOByuWC1WgEA69atAwCcP38eZrM56nUSEVGwuJyyUJKXBs9rm3zHuOZznC4/cyE8r21S3UKFkPwcgHl1kQZub2ziTsjRpvKxyvHJabz2M0vU2wOAjIyMuV9ERETzFlbo/a/7vg8AGL0d3PV4feIO0lMXqEYyyupty5GfudA3enMucqtpPvYWZ6F/dComA2IA4N+ffQiAv4tXKT9z4Yy/OxERaU9YoVdgXoT8zIUhW1Dtl27Oevys+jFvK2auEGr54Muwj/3NRm6BaW1ADBERiRf2Mb3qxzLwnONT7HN+4ete3NI8iPHJ6aATtZW2rb8fe/7jc7xxbmzG42wtH3yJ5xyfoiQvbV7HxmQleWk42jeOlg++nPf65JGZsaiLYuvbb7/F+++/L7oMSoDPP/8cubm5osugJBB26Mkf+s85PvV1V6anLghrkMrzj2Zgb8eIKjCP9o2rZlIJPMFdFmogS6gZUZSanl6Bo33jaPzjWMRhFbi9krw0nnCuUTdu3MAbb7whugxKgOHhYV5OiWLC5PF4PKKLIArp7jmMAICA3fTy5cuoqKhAT09PgosiEQ4ePIiUlBTs3r1b/cQs+whRKJxwmoiIDIOhR0REhsHQIyIiw2DoERGRYTD0iIjIMBh6RERkGAw9IiIyDIYekY6kpqaqbiMjI6JLItIVhh6RDvT19SE1NRUtLS2qK6hnZWWJLo1IVyK6nl5GfZ934untK2d9TeDVxgHvdF7yz8mvUU5hts/5BfZ2jKheF+qSPoB/GjJ5zs6Z5GcujGgKMYfDgfLyctWy1tZWlJWVhb2O+cjOzoYkSWhoaEBNTU1Ctkn6YLfbsX//fpSWloouhUjXYt7SG9u3QXUtvZ4XVsPz2iZVUMqhqLzywplLN5GeukC1Lvmadjs2pKsey/Nublt/v+rad4A3EOXHkc6ZWVZWBo/H47u5XC6Ul5fD4XBE9keIgt1uhyRJcd8O6c/IyAi6urqwfv160aUYQnFxMUwmk+pmt9tDvjY7O1v1OrfbrVpPcXFxyJ+z2+3Izs6Oqr7Abfb29ga9JrD+wG0HLgO8X/qVv4O8ncD1K/8eof5Wc9UWLnnd1dXVUa8jFGHdmyV5ab7L/7jcExibuIP8zIUhW4miWK1WWCyWhMzvWFtbi9bW1rhvh/RnfNw7Mftbb72lOp536NAhwZUlL5vNpvryW1tbGxR8JpMJRUVFvtc1NDTAYrH4PujNZvOs24g09Nxud9A2PR4PXnnllaDXNDQ0+J6vqqoKCuRwWSwWnDx5csbnOzo6VL8/AFVtVqs14m0C3gB2Op1R/exchIVezeZM9I9OAQBOXPgKJXlpQS09rYj3cZPq6mpYLJY5u1Hlb2gzfeuk5HT9+nUAwLJly3zH8pxOJ+rq6tDW1ia4uuRntVphs9lw9uxZ3zL5/2xjY6NvWU1NDWw2myqEhoeHQ64zmgFIRUVFsNlsqm0C3uBR1mWz2VSHRxobG2GxWHDw4MGottnZ2RlVYM7Hnj17fF8iYi3hoedyT/jul+SlYZ/zCwxc+xbPrF2S6FLmJHc5xvP4Wm9vL44cOYLjx4/P+VqO1DO2n/3sZ777GzZswIMPPohz584JrMi4Ojs7UVlZGbR869atcDqdcLvdc35ZnqslqNTb2wtJkrB169ZZX+d0OkO+prKyEkeOHAl7e4E/m8gvV/H+3BU6evOJf/qhr4tTvpbe2MQdkSX5+rVNJlNCuhxfeeUVVFVVhdUN0NjYCI/Hw0EuBrN06dKQyyP50KToyV1tcpi43W5IkoRly5YFvfaBBx4AAIyNjQGA7zi93O0od31G2nK6du0aAGDdunUzvkZet1yDklxrNC220tJSNDU1Rfxz0XC73XH/3E146H1246+++9vW34/+0SlsyUtLdBkzChzMUl5ePuPB6PmS/zPt2rUrLuun5JCe7h3IdeHCBdXyrq6ukB+8NH9Op9P35be8vBySJEU8ilv53pw/fx4AVC3zSA6bXL16NaJtx5LZbEZ2dva8BqWE6+DBg7DZbHEdMS/8PD3Pa5t8V1PXotbWVl93RayVl5ejoaGB39hpVllZWSgsLMTLL7/sWyYPYlF2eVLsyANZXC4XAETVvadscfX09KChoUHVYtLTF5Y9e/aojlXGg3yoZ8+ePXHdjrDQk7szlZYuStHU6E2lWAeT/K2ptrY2aGixvCwRp0qQPpw+fRqAf0aWuro6npyeAFarFVVVVaitrfV98ZU/C0K1vuRuSOXhCrfbjSNHjqCmpgaSJMHtds84wGUmjz76KAB/t2koGRkZqhqUrl69CovFArPZPGfYhvqss1qtGB4ejuuAlq1bt4Z9qGc+Eh56o7fFHrOLhNvt9rXGQpnPuShWq1XVjSrfAPiGGwc28eXRm/HqbiVt+/jjj1WzsVBiyIcflK29qqqqkMe5jh8/jqqqKgD+EDp//jxsNpvv5+T1hDr2NhM5CGY7fcBsNsNms4UcFNfU1BQ08CYwwORgnEk8B7TIx0mPHDmiagQol8Vq1HrEodd+6SZMO7tVt8pTQ77nM+r7YNrZjb0dIwCAja9/BNPObmxpHgx7G/IIT3n9R/vGVY+V24ul6upq1R/cYrHMOopI3pFn2xFjSf62F6/uViIKJodJbW2tb9muXbsgSZLqg9hut4c8Rr9nzx7fIJiNGzeitrY2qokoWltbceTIkaCTtZVfgvfs2QOn06nqJaqurlZ9jslfppXrkQeQzNaFGc8BLWazOWQjwGKxoKqqKqYD+CKahmxs34Z5v6betnzGY3iBzymnKQtHpK8P1NjYGHQOzGzk4cmx7GKSW3uhyCfLS5LE44BECSSHid1uR01NDcxmMyRJgsViUYWh8v+v/H9UkiTfqMuysjLfVIdySzBcZWVlWLduHSwWi+r0A/m4I+D9jHC5XCgoKPBtx2KxBH2ueDyeoNla5ppy0Ww2o6ioKOpTH7TC5JntU5ZmJc+Vmag/oTw3qGHm5lROlRTwN758+TIqKioSMlsOiXfw4EGkpKRg9+7d6idm2UeIQhE+elOP5G5QYPaWWazI5/iUl5ejtbXVGIFHRBQHDL0oyCeJRzoCK1rK/u5EXfGBiOJvrgmb4zHhstFFdEyPiIhiRzlvJiUGQ490a2pqCu+9957oMigBhoeHkZeXJ7oMSgIMPdKtmzdv4s033xRdBiXAZ599hrQ07UxXSPrF0CPdysrKwm9+8xvRZVACyKM3ieaLA1mIiMgwGHpElLSmp6dx8eJFXLlyRXQppBHs3iSipDM9PY0rV65gaGgI09PTWLlypeiSSCMYekSUNALDjigQQ4+IksLFixdnDLvBwUEMDoY/6T3pz8qVK/Hwww/P+TqGHhElBYYahYMDWYgoKaxZs0Z0CaQDbOkRadyhQ4dQV1cX8rn9+/fjxRdfTHBF2pSbm4vc3FxcuXIFAwMDqufC7fqi5MeWHpHGvfjii6orpk9OTuLixYsAgPXr1wuuTntyc3NRUVHBlh+FxJYekQ4dPnwYDz74IDZsmPvCzkalbPlxJCfJGHpEOtTc3Iz9+/eLLkMXcnNzRZdAGsLuTSKdOXToEADwWB5RFBh6RDpz7NgxbN++XXQZRLrE0CPSkb6+Pnz++ed46qmnRJdCpEsMPdKuxYv992/eFFeHhtjtdhQWFnIAC6DeJ5T7CtEsGHqkXRkZ/vvj4+Lq0IiRkRF0dXXh5z//uehStEG5Tyj3FaJZMPRIu9LT/fcZejh8+DAAoLS0VHAlGqHcJ5T7CtEsGHqkXcpv72Nj4urQiObmZg5gUVLuE2zpUZh4nh5pV3a2/35XF/Dss+Jq0YDJyUnRJWhLV5f/vnJfIZoFW3qkXT/9qf9+e7u4OkiblPuEcl8hmgVDj7RrzRpgxQrv/Vu3gM5OsfWQdrz3nnefALz7COfZpDAx9EjblN/g33pLXB2kLadO+e+zlUcRYOiRtpWU+O83NwMXLoirhbThwgXvviDbskVcLaQ7DD3Sts2bgSef9D8+cEBcLaQNyn3gySeBxx8XVwvpDkOPtG/3bv/906c5qMXI2tu9+4BMuW8QhYGnLJD2rVkDVFYCTU3ex7/4BVJOnMDk5CQ6OjrE1kYJ8dlnn+F/L1kC/OIX/oWVlRzAQhEzeTwej+giiOZ0/Trw6KPA1asAgL/l5eHBsTFs/MlPBBdGiSB98glOf/01Mr/+2rtg2TLg3Dlg6VKxhZHuMPRIP3p6gE2bfA+/WrAASyYngZQUgUVR3N25g8klS5A6MeFf1t0NbNworibSLR7TI/3YuBE4dsz3cMn0NPDYY8DlywKLori6fBl47DF14B07xsCjqDH0SF+2bgXq6vyP+/u9wcfBLcmnvd373vb3+5fV1Xn3AaIoMfRIf/btw9i//Zv/8a1b3nO1/uVfeB5fMrhwwftebtnin3UFQPuTTwL79gksjJIBQ490aeKJJ1CZk+Md0CA7fRp45BFgxw7vNFWkL52d3vfukUfUpyUsW4bf7NiBixypSTHAUxZItwb+8R+9M+3v3+8/nQHwztbR3Oy9mnZJCVBY6L30THq695aWJq5o8l7xfHzcexsb876H7e2qVp1PZSVQV4erb76Z+DopKTH0SN+WLgXeeAN4/nnvTB1vv+1/7tYt4MQJ74305cknvSees3VHMcbQo+SwZg3Q1gb84Q/AmTPAu+8CQ0Oiq6JIrFjhbZmXlHBqMYobhh4ll8cf995efRUYGPCG3/CwtxtN7k4L1Y1GibN4sb+7OSPDe1HHAjIAAAEgSURBVAHYkhLgxz8WXRkZAEOPkteaNeweIyIVjt4kIiLDYOgREZFhMPSIiMgwGHpERGQYDD0iIjIMhh4RERkGQ4+IiAyDoUekYXa7HSaTCb29vQCA4uJimEwm3/MmkwnFxcWiyiPSHYYeEREZBmdkIdKwmpoa1NTU+B53dHSonvd4PIkuiUjX2NIjIiLDYOgREZFhMPSIiMgwGHpERGQYDD0iIjIMhh4RERkGT1kg3bp9+zbeeecd0WVQAnzyySd46KGHRJdBSYChR7r0ox/9CKmpqWhraxNdCiXA1NQUHnnkEdFlUBIweXh2KxERGQSP6RERkWEw9IiIyDAYekREZBgMPSIiMgyGHhERGQZDj4iIDIOhR0REhsHQIyIiw/j/nBrpojxQyUgAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Image('../images/MySQL-LIMIT-illustration.png')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13041e81-bc39-4fa8-a2bf-d66941383284",
   "metadata": {
    "tags": []
   },
   "source": [
    "### The LIMIT and ORDER BY clauses"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f50f502f-bb3e-42e9-ad3a-efed543c4476",
   "metadata": {},
   "source": [
    "To ensure the `LIMIT` clause returns an expected output, you should always use it with an `ORDER BY` clause."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7dba080e-91ab-40f6-b35d-1833579c43ad",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL LIMIT clause examples"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "446e40b2-0028-4299-a526-86b4150563a5",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### 1) Using MySQL LIMIT to get the highest or lowest rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6fb3cda3-72b5-4a4b-aec4-80a7ed9052ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT customerNumber, customerName, creditLimit\n",
    "FROM customers\n",
    "ORDER BY creditLimit DESC\n",
    "LIMIT 5\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b8ef70bc-ba0f-4f39-ab75-24bb3016207f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('customers', engine)\n",
    "    [['customerNumber', 'customerName', 'creditLimit']]\n",
    "    .nlargest(n=5, columns='creditLimit')\n",
    "    .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a55404b7-fef8-4450-a18f-60a0687a1ef1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7d7b65ad-252d-4285-a3c7-e615a888b402",
   "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>customerNumber</th>\n",
       "      <th>customerName</th>\n",
       "      <th>creditLimit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>141</td>\n",
       "      <td>Euro+ Shopping Channel</td>\n",
       "      <td>227600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>124</td>\n",
       "      <td>Mini Gifts Distributors Ltd.</td>\n",
       "      <td>210500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>298</td>\n",
       "      <td>Vida Sport, Ltd</td>\n",
       "      <td>141300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>151</td>\n",
       "      <td>Muscle Machine Inc</td>\n",
       "      <td>138500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>187</td>\n",
       "      <td>AV Stores, Co.</td>\n",
       "      <td>136800.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerNumber                  customerName  creditLimit\n",
       "0             141        Euro+ Shopping Channel     227600.0\n",
       "1             124  Mini Gifts Distributors Ltd.     210500.0\n",
       "2             298               Vida Sport, Ltd     141300.0\n",
       "3             151            Muscle Machine Inc     138500.0\n",
       "4             187                AV Stores, Co.     136800.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5be62f7b-bebb-4afa-8730-35f1680305f1",
   "metadata": {},
   "source": [
    "- Find five customers who have the lowest credits"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "3fb62626-975f-42c5-a89e-89337c638635",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT customerNumber, customerName, creditLimit\n",
    "FROM customers\n",
    "ORDER BY creditLimit, customerNumber\n",
    "LIMIT 5\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ee5f5839-4931-48ea-a4ad-e49c38b9690a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('customers', engine)\n",
    "    [['customerNumber', 'customerName', 'creditLimit']]\n",
    "    .nsmallest(n=5, columns=['creditLimit', 'customerNumber'])\n",
    "    .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "df73122b-69be-4d55-a725-89e004b4a760",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "22c0e983-2e92-44db-b29b-1499929004e1",
   "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>customerNumber</th>\n",
       "      <th>customerName</th>\n",
       "      <th>creditLimit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>125</td>\n",
       "      <td>Havel &amp; Zbyszek Co</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>168</td>\n",
       "      <td>American Souvenirs Inc</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>169</td>\n",
       "      <td>Porto Imports Co.</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>206</td>\n",
       "      <td>Asian Shopping Network, Co</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>223</td>\n",
       "      <td>Natürlich Autos</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerNumber                customerName  creditLimit\n",
       "0             125          Havel & Zbyszek Co          0.0\n",
       "1             168      American Souvenirs Inc          0.0\n",
       "2             169           Porto Imports Co.          0.0\n",
       "3             206  Asian Shopping Network, Co          0.0\n",
       "4             223             Natürlich Autos          0.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3c1eb88-be81-457c-8062-7bd9c4e729c6",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### 2) Using MySQL LIMIT clause for pagination"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "1d4c6e50-35f0-47b5-ba0e-96f6303b44f3",
   "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>COUNT(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>122</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   COUNT(*)\n",
       "0       122"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the total rows from the customers table:\n",
    "string = '''\n",
    "SELECT COUNT(*)\n",
    "FROM customers;\n",
    "'''\n",
    "\n",
    "pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "2e4f815d-84cc-47b8-bae4-945496977e1e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(122, 13)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_table('customers', engine).shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7387931e-99e3-46dc-80fc-7307305a3277",
   "metadata": {},
   "source": [
    "- Select the first 10 customers sorted by names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "f70e213b-a0d7-447a-b60f-99d900d543fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT customerNumber, customerName\n",
    "FROM customers\n",
    "ORDER BY customerName\n",
    "# LIMIT 10\n",
    "LIMIT 10 OFFSET 0\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "0c464c18-68d2-49c2-944f-219a75fe9f7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('customers', engine)\n",
    "    .sort_values(\n",
    "        by='customerName',\n",
    "        ascending=True,\n",
    "        key = lambda col: col.str.lower())\n",
    "    [['customerNumber', 'customerName']]\n",
    "    .head(10)\n",
    "    .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "24eca597-3202-4f07-ab88-53cf772db489",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "0b33290d-baa9-4d8a-9069-e28d5e9f84ee",
   "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>customerNumber</th>\n",
       "      <th>customerName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>242</td>\n",
       "      <td>Alpha Cognac</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>168</td>\n",
       "      <td>American Souvenirs Inc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>249</td>\n",
       "      <td>Amica Models &amp; Co.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>237</td>\n",
       "      <td>ANG Resellers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>276</td>\n",
       "      <td>Anna's Decorations, Ltd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>465</td>\n",
       "      <td>Anton Designs, Ltd.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>206</td>\n",
       "      <td>Asian Shopping Network, Co</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>348</td>\n",
       "      <td>Asian Treasures, Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>103</td>\n",
       "      <td>Atelier graphique</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>471</td>\n",
       "      <td>Australian Collectables, Ltd</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerNumber                  customerName\n",
       "0             242                  Alpha Cognac\n",
       "1             168        American Souvenirs Inc\n",
       "2             249            Amica Models & Co.\n",
       "3             237                 ANG Resellers\n",
       "4             276       Anna's Decorations, Ltd\n",
       "5             465           Anton Designs, Ltd.\n",
       "6             206    Asian Shopping Network, Co\n",
       "7             348         Asian Treasures, Inc.\n",
       "8             103             Atelier graphique\n",
       "9             471  Australian Collectables, Ltd"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98411fbf-fc23-4e90-a5a6-94a72580d84e",
   "metadata": {},
   "source": [
    "- Select the 10th - 19th customers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "32057bc5-ec6d-4fe2-99fe-b63d1ade148c",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT customerNumber, customerName\n",
    "FROM customers\n",
    "ORDER BY customerName\n",
    "# LIMIT 9, 10\n",
    "LIMIT 10 OFFSET 9\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "ceab56e3-a9ff-4678-93e1-5d8692b22d7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('customers', engine)\n",
    "    .sort_values(\n",
    "        by='customerName',\n",
    "        ascending=True,\n",
    "        key=lambda col: col.str.lower())\n",
    "     [['customerNumber', 'customerName']]\n",
    "    .head(19)\n",
    "    .tail(10)\n",
    "    .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "6a7eb670-5085-4b17-a7a9-5e1474330b78",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "15e78a8e-c5b2-4002-a912-e088391d041f",
   "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>customerNumber</th>\n",
       "      <th>customerName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>471</td>\n",
       "      <td>Australian Collectables, Ltd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>114</td>\n",
       "      <td>Australian Collectors, Co.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>333</td>\n",
       "      <td>Australian Gift Network, Co</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>256</td>\n",
       "      <td>Auto Associés &amp; Cie.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>406</td>\n",
       "      <td>Auto Canal+ Petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>198</td>\n",
       "      <td>Auto-Moto Classics Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>187</td>\n",
       "      <td>AV Stores, Co.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>121</td>\n",
       "      <td>Baane Mini Imports</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>415</td>\n",
       "      <td>Bavarian Collectables Imports, Co.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>293</td>\n",
       "      <td>BG&amp;E Collectables</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerNumber                        customerName\n",
       "0             471        Australian Collectables, Ltd\n",
       "1             114          Australian Collectors, Co.\n",
       "2             333         Australian Gift Network, Co\n",
       "3             256                Auto Associés & Cie.\n",
       "4             406                   Auto Canal+ Petit\n",
       "5             198             Auto-Moto Classics Inc.\n",
       "6             187                      AV Stores, Co.\n",
       "7             121                  Baane Mini Imports\n",
       "8             415  Bavarian Collectables Imports, Co.\n",
       "9             293                   BG&E Collectables"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f5358b0-3132-4317-bda4-1e1b9ca6d3c6",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### 3) Using MySQL LIMIT to get the n<sup>th</sup> highest or lowest value"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "914ae2e6-3083-48a6-bdeb-a53e374da62e",
   "metadata": {},
   "source": [
    "- Find the customer who has second highest credit:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "6966efdc-c54b-4308-b4d8-cf0302369657",
   "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>customerName</th>\n",
       "      <th>creditLimit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mini Gifts Distributors Ltd.</td>\n",
       "      <td>210500.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   customerName  creditLimit\n",
       "0  Mini Gifts Distributors Ltd.     210500.0"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "SELECT customerName, creditLimit\n",
    "FROM customers\n",
    "ORDER BY creditLimit DESC\n",
    "LIMIT 1, 1\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "4f58b9b8-b959-4aa0-bf36-06530cadd6a1",
   "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>customerName</th>\n",
       "      <th>creditLimit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mini Gifts Distributors Ltd.</td>\n",
       "      <td>210500.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   customerName  creditLimit\n",
       "0  Mini Gifts Distributors Ltd.     210500.0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(pd.read_sql_table('customers', engine)\n",
    " [['customerName', 'creditLimit']]\n",
    " .nlargest(n=2, columns='creditLimit')\n",
    " .nsmallest(n=1, columns='creditLimit')\n",
    " .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "177f8ca9-232e-4fe9-9da8-7bf83a7da779",
   "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>customerName</th>\n",
       "      <th>creditLimit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Euro+ Shopping Channel</td>\n",
       "      <td>227600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mini Gifts Distributors Ltd.</td>\n",
       "      <td>210500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Vida Sport, Ltd</td>\n",
       "      <td>141300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Muscle Machine Inc</td>\n",
       "      <td>138500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AV Stores, Co.</td>\n",
       "      <td>136800.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   customerName  creditLimit\n",
       "0        Euro+ Shopping Channel     227600.0\n",
       "1  Mini Gifts Distributors Ltd.     210500.0\n",
       "2               Vida Sport, Ltd     141300.0\n",
       "3            Muscle Machine Inc     138500.0\n",
       "4                AV Stores, Co.     136800.0"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# double check the result\n",
    "string = '''\n",
    "SELECT customerName, creditLimit\n",
    "FROM customers\n",
    "ORDER BY creditLimit DESC\n",
    "LIMIT 5\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30b59a76-adb4-4026-82f5-a6de67af7469",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL LIMIT & DISTINCT clauses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "978be7e1-3f06-4bf8-9960-ca371fa90fbf",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT DISTINCT state\n",
    "FROM customers\n",
    "WHERE state IS NOT NULL\n",
    "LIMIT 5\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "31f83008-51cd-4f69-8319-bffa06ee0289",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table(\n",
    "        'customers', engine, columns=['state'])\n",
    "    .dropna(axis=0)\n",
    "    .drop_duplicates(ignore_index=True)\n",
    "    .head(5)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "f0ad704f-b705-4374-b0d8-28cf9b0c8fae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "01ab3890-fd75-49a0-abbf-bc23ae810454",
   "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>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Victoria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      state\n",
       "0        NV\n",
       "1  Victoria\n",
       "2        CA\n",
       "3        NY\n",
       "4        PA"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  }
 ],
 "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
}