sql/novice/02-sort-dup.ipynb

   1 {
   2  "metadata": {
   3   "name": ""
   4  },
   5  "nbformat": 3,
   6  "nbformat_minor": 0,
   7  "worksheets": [
   8   {
   9    "cells": [
  10     {
  11      "cell_type": "heading",
  12      "level": 1,
  13      "metadata": {},
  14      "source": [
  15       "Sorting and Removing Duplicates"
  16      ]
  17     },
  18     {
  19      "cell_type": "markdown",
  20      "metadata": {},
  21      "source": [
  22       "Data is often redundant,\n",
  23       "so queries often return redundant information.\n",
  24       "For example,\n",
  25       "if we select the quantitites that have been measured\n",
  26       "from the `survey` table,\n",
  27       "we get this:"
  28      ]
  29     },
  30     {
  31      "cell_type": "code",
  32      "collapsed": false,
  33      "input": [
  34       "%load_ext sqlitemagic"
  35      ],
  36      "language": "python",
  37      "metadata": {},
  38      "outputs": [],
  39      "prompt_number": 1
  40     },
  41     {
  42      "cell_type": "code",
  43      "collapsed": false,
  44      "input": [
  45       "%%sqlite survey.db\n",
  46       "select quant from Survey;"
  47      ],
  48      "language": "python",
  49      "metadata": {},
  50      "outputs": [
  51       {
  52        "html": [
  53         "<table>\n",
  54         "<tr><td>rad</td></tr>\n",
  55         "<tr><td>sal</td></tr>\n",
  56         "<tr><td>rad</td></tr>\n",
  57         "<tr><td>sal</td></tr>\n",
  58         "<tr><td>rad</td></tr>\n",
  59         "<tr><td>sal</td></tr>\n",
  60         "<tr><td>temp</td></tr>\n",
  61         "<tr><td>rad</td></tr>\n",
  62         "<tr><td>sal</td></tr>\n",
  63         "<tr><td>temp</td></tr>\n",
  64         "<tr><td>rad</td></tr>\n",
  65         "<tr><td>temp</td></tr>\n",
  66         "<tr><td>sal</td></tr>\n",
  67         "<tr><td>rad</td></tr>\n",
  68         "<tr><td>sal</td></tr>\n",
  69         "<tr><td>temp</td></tr>\n",
  70         "<tr><td>sal</td></tr>\n",
  71         "<tr><td>rad</td></tr>\n",
  72         "<tr><td>sal</td></tr>\n",
  73         "<tr><td>sal</td></tr>\n",
  74         "<tr><td>rad</td></tr>\n",
  75         "</table>"
  76        ],
  77        "metadata": {},
  78        "output_type": "display_data",
  79        "text": [
  80         "<IPython.core.display.HTML at 0x1023cbe10>"
  81        ]
  82       }
  83      ],
  84      "prompt_number": 2
  85     },
  86     {
  87      "cell_type": "markdown",
  88      "metadata": {},
  89      "source": [
  90       "We can eliminate the redundant output\n",
  91       "to make the result more readable\n",
  92       "by adding the `distinct` keyword\n",
  93       "to our query:"
  94      ]
  95     },
  96     {
  97      "cell_type": "code",
  98      "collapsed": false,
  99      "input": [
 100       "%%sqlite survey.db\n",
 101       "select distinct quant from Survey;"
 102      ],
 103      "language": "python",
 104      "metadata": {},
 105      "outputs": [
 106       {
 107        "html": [
 108         "<table>\n",
 109         "<tr><td>rad</td></tr>\n",
 110         "<tr><td>sal</td></tr>\n",
 111         "<tr><td>temp</td></tr>\n",
 112         "</table>"
 113        ],
 114        "metadata": {},
 115        "output_type": "display_data",
 116        "text": [
 117         "<IPython.core.display.HTML at 0x1023cbf10>"
 118        ]
 119       }
 120      ],
 121      "prompt_number": 3
 122     },
 123     {
 124      "cell_type": "markdown",
 125      "metadata": {},
 126      "source": [
 127       "If we select more than one column&mdash;for example,\n",
 128       "both the survey site ID and the quantity measured&mdash;then\n",
 129       "the distinct pairs of values are returned:"
 130      ]
 131     },
 132     {
 133      "cell_type": "code",
 134      "collapsed": false,
 135      "input": [
 136       "%%sqlite survey.db\n",
 137       "select distinct taken, quant from Survey;"
 138      ],
 139      "language": "python",
 140      "metadata": {},
 141      "outputs": [
 142       {
 143        "html": [
 144         "<table>\n",
 145         "<tr><td>619</td><td>rad</td></tr>\n",
 146         "<tr><td>619</td><td>sal</td></tr>\n",
 147         "<tr><td>622</td><td>rad</td></tr>\n",
 148         "<tr><td>622</td><td>sal</td></tr>\n",
 149         "<tr><td>734</td><td>rad</td></tr>\n",
 150         "<tr><td>734</td><td>sal</td></tr>\n",
 151         "<tr><td>734</td><td>temp</td></tr>\n",
 152         "<tr><td>735</td><td>rad</td></tr>\n",
 153         "<tr><td>735</td><td>sal</td></tr>\n",
 154         "<tr><td>735</td><td>temp</td></tr>\n",
 155         "<tr><td>751</td><td>rad</td></tr>\n",
 156         "<tr><td>751</td><td>temp</td></tr>\n",
 157         "<tr><td>751</td><td>sal</td></tr>\n",
 158         "<tr><td>752</td><td>rad</td></tr>\n",
 159         "<tr><td>752</td><td>sal</td></tr>\n",
 160         "<tr><td>752</td><td>temp</td></tr>\n",
 161         "<tr><td>837</td><td>rad</td></tr>\n",
 162         "<tr><td>837</td><td>sal</td></tr>\n",
 163         "<tr><td>844</td><td>rad</td></tr>\n",
 164         "</table>"
 165        ],
 166        "metadata": {},
 167        "output_type": "display_data",
 168        "text": [
 169         "<IPython.core.display.HTML at 0x1023cbf10>"
 170        ]
 171       }
 172      ],
 173      "prompt_number": 4
 174     },
 175     {
 176      "cell_type": "markdown",
 177      "metadata": {},
 178      "source": [
 179       "Notice in both cases that duplicates are removed\n",
 180       "even if they didn't appear to be adjacent in the database.\n",
 181       "Again,\n",
 182       "it's important to remember that rows aren't actually ordered:\n",
 183       "they're just displayed that way."
 184      ]
 185     },
 186     {
 187      "cell_type": "markdown",
 188      "metadata": {},
 189      "source": [
 190       "### Challenges\n",
 191       "\n",
 192       "1.  Write a query that selects distinct dates from the `Site` table."
 193      ]
 194     },
 195     {
 196      "cell_type": "markdown",
 197      "metadata": {},
 198      "source": [
 199       "As we mentioned earlier,\n",
 200       "database records are not stored in any particular order.\n",
 201       "This means that query results aren't necessarily sorted,\n",
 202       "and even if they are,\n",
 203       "we often want to sort them in a different way,\n",
 204       "e.g., by the name of the project instead of by the name of the scientist.\n",
 205       "We can do this in SQL by adding an `order by` clause to our query:"
 206      ]
 207     },
 208     {
 209      "cell_type": "code",
 210      "collapsed": false,
 211      "input": [
 212       "%%sqlite survey.db\n",
 213       "select * from Person order by ident;"
 214      ],
 215      "language": "python",
 216      "metadata": {},
 217      "outputs": [
 218       {
 219        "html": [
 220         "<table>\n",
 221         "<tr><td>danforth</td><td>Frank</td><td>Danforth</td></tr>\n",
 222         "<tr><td>dyer</td><td>William</td><td>Dyer</td></tr>\n",
 223         "<tr><td>lake</td><td>Anderson</td><td>Lake</td></tr>\n",
 224         "<tr><td>pb</td><td>Frank</td><td>Pabodie</td></tr>\n",
 225         "<tr><td>roe</td><td>Valentina</td><td>Roerich</td></tr>\n",
 226         "</table>"
 227        ],
 228        "metadata": {},
 229        "output_type": "display_data",
 230        "text": [
 231         "<IPython.core.display.HTML at 0x1023cbe90>"
 232        ]
 233       }
 234      ],
 235      "prompt_number": 5
 236     },
 237     {
 238      "cell_type": "markdown",
 239      "metadata": {},
 240      "source": [
 241       "By default,\n",
 242       "results are sorted in ascending order\n",
 243       "(i.e.,\n",
 244       "from least to greatest).\n",
 245       "We can sort in the opposite order using `desc` (for \"descending\"):"
 246      ]
 247     },
 248     {
 249      "cell_type": "code",
 250      "collapsed": false,
 251      "input": [
 252       "%%sqlite survey.db\n",
 253       "select * from person order by ident desc;"
 254      ],
 255      "language": "python",
 256      "metadata": {},
 257      "outputs": [
 258       {
 259        "html": [
 260         "<table>\n",
 261         "<tr><td>roe</td><td>Valentina</td><td>Roerich</td></tr>\n",
 262         "<tr><td>pb</td><td>Frank</td><td>Pabodie</td></tr>\n",
 263         "<tr><td>lake</td><td>Anderson</td><td>Lake</td></tr>\n",
 264         "<tr><td>dyer</td><td>William</td><td>Dyer</td></tr>\n",
 265         "<tr><td>danforth</td><td>Frank</td><td>Danforth</td></tr>\n",
 266         "</table>"
 267        ],
 268        "metadata": {},
 269        "output_type": "display_data",
 270        "text": [
 271         "<IPython.core.display.HTML at 0x1023cbe10>"
 272        ]
 273       }
 274      ],
 275      "prompt_number": 6
 276     },
 277     {
 278      "cell_type": "markdown",
 279      "metadata": {},
 280      "source": [
 281       "(And if we want to make it clear that we're sorting in ascending order,\n",
 282       "we can use `asc` instead of `desc`.)\n",
 283       "  \n",
 284       "We can also sort on several fields at once.\n",
 285       "For example,\n",
 286       "this query sorts results first in ascending order by `taken`,\n",
 287       "and then in descending order by `person`\n",
 288       "within each group of equal `taken` values:"
 289      ]
 290     },
 291     {
 292      "cell_type": "code",
 293      "collapsed": false,
 294      "input": [
 295       "%%sqlite survey.db\n",
 296       "select taken, person from Survey order by taken asc, person desc;"
 297      ],
 298      "language": "python",
 299      "metadata": {},
 300      "outputs": [
 301       {
 302        "html": [
 303         "<table>\n",
 304         "<tr><td>619</td><td>dyer</td></tr>\n",
 305         "<tr><td>619</td><td>dyer</td></tr>\n",
 306         "<tr><td>622</td><td>dyer</td></tr>\n",
 307         "<tr><td>622</td><td>dyer</td></tr>\n",
 308         "<tr><td>734</td><td>pb</td></tr>\n",
 309         "<tr><td>734</td><td>pb</td></tr>\n",
 310         "<tr><td>734</td><td>lake</td></tr>\n",
 311         "<tr><td>735</td><td>pb</td></tr>\n",
 312         "<tr><td>735</td><td>None</td></tr>\n",
 313         "<tr><td>735</td><td>None</td></tr>\n",
 314         "<tr><td>751</td><td>pb</td></tr>\n",
 315         "<tr><td>751</td><td>pb</td></tr>\n",
 316         "<tr><td>751</td><td>lake</td></tr>\n",
 317         "<tr><td>752</td><td>roe</td></tr>\n",
 318         "<tr><td>752</td><td>lake</td></tr>\n",
 319         "<tr><td>752</td><td>lake</td></tr>\n",
 320         "<tr><td>752</td><td>lake</td></tr>\n",
 321         "<tr><td>837</td><td>roe</td></tr>\n",
 322         "<tr><td>837</td><td>lake</td></tr>\n",
 323         "<tr><td>837</td><td>lake</td></tr>\n",
 324         "<tr><td>844</td><td>roe</td></tr>\n",
 325         "</table>"
 326        ],
 327        "metadata": {},
 328        "output_type": "display_data",
 329        "text": [
 330         "<IPython.core.display.HTML at 0x1023c4090>"
 331        ]
 332       }
 333      ],
 334      "prompt_number": 7
 335     },
 336     {
 337      "cell_type": "markdown",
 338      "metadata": {},
 339      "source": [
 340       "This is easier to understand if we also remove duplicates:"
 341      ]
 342     },
 343     {
 344      "cell_type": "code",
 345      "collapsed": false,
 346      "input": [
 347       "%%sqlite survey.db\n",
 348       "select distinct taken, person from Survey order by taken asc, person desc;"
 349      ],
 350      "language": "python",
 351      "metadata": {},
 352      "outputs": [
 353       {
 354        "html": [
 355         "<table>\n",
 356         "<tr><td>619</td><td>dyer</td></tr>\n",
 357         "<tr><td>622</td><td>dyer</td></tr>\n",
 358         "<tr><td>734</td><td>pb</td></tr>\n",
 359         "<tr><td>734</td><td>lake</td></tr>\n",
 360         "<tr><td>735</td><td>pb</td></tr>\n",
 361         "<tr><td>735</td><td>None</td></tr>\n",
 362         "<tr><td>751</td><td>pb</td></tr>\n",
 363         "<tr><td>751</td><td>lake</td></tr>\n",
 364         "<tr><td>752</td><td>roe</td></tr>\n",
 365         "<tr><td>752</td><td>lake</td></tr>\n",
 366         "<tr><td>837</td><td>roe</td></tr>\n",
 367         "<tr><td>837</td><td>lake</td></tr>\n",
 368         "<tr><td>844</td><td>roe</td></tr>\n",
 369         "</table>"
 370        ],
 371        "metadata": {},
 372        "output_type": "display_data",
 373        "text": [
 374         "<IPython.core.display.HTML at 0x1023c96d0>"
 375        ]
 376       }
 377      ],
 378      "prompt_number": 8
 379     },
 380     {
 381      "cell_type": "markdown",
 382      "metadata": {},
 383      "source": [
 384       "### Challenges\n",
 385       "\n",
 386       "1.  Write a query that displays scientists' full names, but orders them by surname."
 387      ]
 388     }
 389    ],
 390    "metadata": {}
 391   }
 392  ]
 393 }