mirror of
https://github.com/redoules/redoules.github.io.git
synced 2025-12-12 15:59:34 +00:00
393 lines
14 KiB
HTML
393 lines
14 KiB
HTML
<!DOCTYPE html>
|
|
<html lang="fr">
|
|
|
|
<head>
|
|
<meta charset="utf-8">
|
|
<meta http-equiv="X-UA-Compatible" content="IE=edge">
|
|
<meta name="viewport" content="width=device-width, initial-scale=1">
|
|
<!-- The above 3 meta tags *must* come first in the head; any other head content must come *after* these tags -->
|
|
<meta name="description" content="Data Science for Political and Social Phenomena">
|
|
<meta name="author" content="Guillaume Redoulès">
|
|
<link rel="icon" href="../favicon.ico">
|
|
|
|
<title>Sorting results - SQL</title>
|
|
|
|
<!-- JQuery -->
|
|
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
|
|
<script>
|
|
window.jQuery || document.write('<script src="../theme/js/jquery.min.js"><\/script>')
|
|
</script>
|
|
|
|
<!-- Bootstrap core CSS -->
|
|
<link rel="stylesheet" href="../theme/css/bootstrap.css" />
|
|
<!-- IE10 viewport hack for Surface/desktop Windows 8 bug -->
|
|
<link rel="stylesheet" type="text/css" href="../theme/css/ie10-viewport-bug-workaround.css" />
|
|
<!-- Custom styles for this template -->
|
|
<link rel="stylesheet" type="text/css" href="../theme/css/style.css" />
|
|
<link rel="stylesheet" type="text/css" href="../theme/css/notebooks.css" />
|
|
<link href='https://fonts.googleapis.com/css?family=PT+Serif:400,700|Roboto:400,500,700' rel='stylesheet' type='text/css'>
|
|
|
|
<!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
|
|
<!--[if lt IE 9]>
|
|
<script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
|
|
<script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
|
|
<![endif]-->
|
|
|
|
|
|
<meta name="tags" content="Basics" />
|
|
|
|
|
|
</head>
|
|
|
|
<body>
|
|
|
|
<div class="navbar navbar-fixed-top">
|
|
<div class="container">
|
|
<div class="navbar-header">
|
|
<button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
|
|
<span class="icon-bar"></span>
|
|
<span class="icon-bar"></span>
|
|
<span class="icon-bar"></span>
|
|
</button>
|
|
<a class="navbar-brand" href="..">Guillaume Redoulès</a>
|
|
</div>
|
|
<div class="navbar-collapse collapse" id="searchbar">
|
|
|
|
<ul class="nav navbar-nav navbar-right">
|
|
<li class="dropdown">
|
|
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">About<span class="caret"></span></a>
|
|
<ul class="dropdown-menu">
|
|
<li><a href="../pages/about.html">About Guillaume</a></li>
|
|
<li><a href="https://github.com/redoules">GitHub</a></li>
|
|
<li><a href="https://www.linkedin.com/in/guillaume-redoul%C3%A8s-33923860/">LinkedIn</a></li>
|
|
</ul>
|
|
</li>
|
|
<li class="dropdown">
|
|
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">Data Science<span class="caret"></span></a>
|
|
<ul class="dropdown-menu">
|
|
<li><a href="..#Blog">Blog</a></li>
|
|
<li><a href="..#Python">Python</a></li>
|
|
<li><a href="..#Bash">Bash</a></li>
|
|
<li><a href="..#SQL">SQL</a></li>
|
|
<li><a href="..#Mathematics">Mathematics</a></li>
|
|
<li><a href="..#Machine_Learning">Machine Learning</a></li>
|
|
<li><a href="..#Projects">Projects</a></li>
|
|
</ul>
|
|
</li>
|
|
<li class="dropdown">
|
|
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">Projects<span class="caret"></span></a>
|
|
<ul class="dropdown-menu">
|
|
<li><a href="https://github.com/redoules/redoules.github.io">Notes (Github)</a></li>
|
|
</ul>
|
|
</li>
|
|
|
|
<!--<li class="dropdown">
|
|
<a href="../feeds/blog.rss.xml">Blog RSS</a>
|
|
</li>-->
|
|
|
|
|
|
</ul>
|
|
|
|
<form class="navbar-form" action="../search.html" onsubmit="return validateForm(this.elements['q'].value);">
|
|
<div class="form-group" style="display:inline;">
|
|
<div class="input-group" style="display:table;">
|
|
<span class="input-group-addon" style="width:1%;"><span class="glyphicon glyphicon-search"></span></span>
|
|
<input class="form-control search-query" name="q" id="tipue_search_input" placeholder="e.g. scikit KNN, pandas merge" required autocomplete="off" type="text">
|
|
</div>
|
|
</div>
|
|
</form>
|
|
|
|
</div>
|
|
<!--/.nav-collapse -->
|
|
</div>
|
|
</div>
|
|
|
|
|
|
|
|
<!-- end of header section -->
|
|
<div class="container">
|
|
<!-- <div class="alert alert-warning" role="alert">
|
|
Did you find this page useful? Please do me a quick favor and <a href="#" class="alert-link">endorse me for data science on LinkedIn</a>.
|
|
</div> -->
|
|
<section id="content" class="body">
|
|
<header>
|
|
<h1>
|
|
Sorting results
|
|
</h1>
|
|
<ol class="breadcrumb">
|
|
<li>
|
|
<time class="published" datetime="2018-08-04T16:49:00+02:00">
|
|
04 août 2018
|
|
</time>
|
|
</li>
|
|
<li>SQL</li>
|
|
<li>Basics</li>
|
|
</ol>
|
|
</header>
|
|
<div class='article_content'>
|
|
<h1>Sorting results in SQL</h1>
|
|
<p>Sorting results can be achieved by using a modifier command at the end of the SQL querry</p>
|
|
<div class="highlight"><pre><span></span><span class="c1">#load the extension</span>
|
|
<span class="o">%</span><span class="n">load_ext</span> <span class="n">sql</span>
|
|
<span class="c1">#connect to the database</span>
|
|
<span class="o">%</span><span class="n">sql</span> <span class="n">sqlite</span><span class="p">:</span><span class="o">///</span><span class="n">mydatabase</span><span class="o">.</span><span class="n">db</span>
|
|
</pre></div>
|
|
|
|
|
|
<div class="highlight"><pre><span></span><span class="err">'Connected: @mydatabase.db'</span>
|
|
</pre></div>
|
|
|
|
|
|
<p>The results can be sorted with the command ORDER BY</p>
|
|
<p>SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]</p>
|
|
<p>Let's show an example where we extract the fruits that are either yellow or red</p>
|
|
<div class="highlight"><pre><span></span><span class="o">%</span><span class="n">sql</span> <span class="n">SELECT</span> <span class="o">*</span> <span class="n">FROM</span> <span class="n">tutyfrutty</span> <span class="n">WHERE</span> <span class="n">color</span> <span class="o">=</span> <span class="s2">"yellow"</span> <span class="n">OR</span> <span class="n">color</span> <span class="o">=</span> <span class="s2">"red"</span>
|
|
</pre></div>
|
|
|
|
|
|
<div class="highlight"><pre><span></span><span class="err"> * sqlite:///mydatabase.db</span>
|
|
<span class="err">Done.</span>
|
|
</pre></div>
|
|
|
|
|
|
<table>
|
|
<tr>
|
|
<th>index</th>
|
|
<th>fruit</th>
|
|
<th>color</th>
|
|
<th>kcal</th>
|
|
</tr>
|
|
<tr>
|
|
<td>0</td>
|
|
<td>Banana</td>
|
|
<td>yellow</td>
|
|
<td>89</td>
|
|
</tr>
|
|
<tr>
|
|
<td>2</td>
|
|
<td>Apple</td>
|
|
<td>red</td>
|
|
<td>52</td>
|
|
</tr>
|
|
<tr>
|
|
<td>3</td>
|
|
<td>lemon</td>
|
|
<td>yellow</td>
|
|
<td>15</td>
|
|
</tr>
|
|
<tr>
|
|
<td>7</td>
|
|
<td>Cranberry</td>
|
|
<td>red</td>
|
|
<td>308</td>
|
|
</tr>
|
|
</table>
|
|
|
|
<h3>Ascending sort</h3>
|
|
<div class="highlight"><pre><span></span><span class="o">%</span><span class="n">sql</span> <span class="n">SELECT</span> <span class="o">*</span> <span class="n">FROM</span> <span class="n">tutyfrutty</span> <span class="n">WHERE</span> <span class="n">color</span> <span class="o">=</span> <span class="s2">"yellow"</span> <span class="n">OR</span> <span class="n">color</span> <span class="o">=</span> <span class="s2">"red"</span> <span class="n">ORDER</span> <span class="n">BY</span> <span class="n">kcal</span> <span class="n">ASC</span>
|
|
</pre></div>
|
|
|
|
|
|
<div class="highlight"><pre><span></span><span class="err"> * sqlite:///mydatabase.db</span>
|
|
<span class="err">Done.</span>
|
|
</pre></div>
|
|
|
|
|
|
<table>
|
|
<tr>
|
|
<th>index</th>
|
|
<th>fruit</th>
|
|
<th>color</th>
|
|
<th>kcal</th>
|
|
</tr>
|
|
<tr>
|
|
<td>3</td>
|
|
<td>lemon</td>
|
|
<td>yellow</td>
|
|
<td>15</td>
|
|
</tr>
|
|
<tr>
|
|
<td>2</td>
|
|
<td>Apple</td>
|
|
<td>red</td>
|
|
<td>52</td>
|
|
</tr>
|
|
<tr>
|
|
<td>0</td>
|
|
<td>Banana</td>
|
|
<td>yellow</td>
|
|
<td>89</td>
|
|
</tr>
|
|
<tr>
|
|
<td>7</td>
|
|
<td>Cranberry</td>
|
|
<td>red</td>
|
|
<td>308</td>
|
|
</tr>
|
|
</table>
|
|
|
|
<h3>descending sort</h3>
|
|
<div class="highlight"><pre><span></span><span class="o">%</span><span class="n">sql</span> <span class="n">SELECT</span> <span class="o">*</span> <span class="n">FROM</span> <span class="n">tutyfrutty</span> <span class="n">WHERE</span> <span class="n">color</span> <span class="o">=</span> <span class="s2">"yellow"</span> <span class="n">OR</span> <span class="n">color</span> <span class="o">=</span> <span class="s2">"red"</span> <span class="n">ORDER</span> <span class="n">BY</span> <span class="n">kcal</span> <span class="n">DESC</span>
|
|
</pre></div>
|
|
|
|
|
|
<div class="highlight"><pre><span></span><span class="err"> * sqlite:///mydatabase.db</span>
|
|
<span class="err">Done.</span>
|
|
</pre></div>
|
|
|
|
|
|
<table>
|
|
<tr>
|
|
<th>index</th>
|
|
<th>fruit</th>
|
|
<th>color</th>
|
|
<th>kcal</th>
|
|
</tr>
|
|
<tr>
|
|
<td>7</td>
|
|
<td>Cranberry</td>
|
|
<td>red</td>
|
|
<td>308</td>
|
|
</tr>
|
|
<tr>
|
|
<td>0</td>
|
|
<td>Banana</td>
|
|
<td>yellow</td>
|
|
<td>89</td>
|
|
</tr>
|
|
<tr>
|
|
<td>2</td>
|
|
<td>Apple</td>
|
|
<td>red</td>
|
|
<td>52</td>
|
|
</tr>
|
|
<tr>
|
|
<td>3</td>
|
|
<td>lemon</td>
|
|
<td>yellow</td>
|
|
<td>15</td>
|
|
</tr>
|
|
</table>
|
|
|
|
<h3>Sort by multiple columns</h3>
|
|
<p>You can sort by more than one column. Just specify multiple columns in the ORDER BY keyword. In the example, we will sort alphabetically on the color column first and sort alphabetically on the fruit column</p>
|
|
<div class="highlight"><pre><span></span><span class="o">%</span><span class="n">sql</span> <span class="n">SELECT</span> <span class="o">*</span> <span class="n">FROM</span> <span class="n">tutyfrutty</span> <span class="n">ORDER</span> <span class="n">BY</span> <span class="n">color</span><span class="p">,</span> <span class="n">fruit</span> <span class="n">ASC</span>
|
|
</pre></div>
|
|
|
|
|
|
<div class="highlight"><pre><span></span><span class="err"> * sqlite:///mydatabase.db</span>
|
|
<span class="err">Done.</span>
|
|
</pre></div>
|
|
|
|
|
|
<table>
|
|
<tr>
|
|
<th>index</th>
|
|
<th>fruit</th>
|
|
<th>color</th>
|
|
<th>kcal</th>
|
|
</tr>
|
|
<tr>
|
|
<td>4</td>
|
|
<td>lime</td>
|
|
<td>green</td>
|
|
<td>30</td>
|
|
</tr>
|
|
<tr>
|
|
<td>1</td>
|
|
<td>Orange</td>
|
|
<td>orange</td>
|
|
<td>47</td>
|
|
</tr>
|
|
<tr>
|
|
<td>5</td>
|
|
<td>plum</td>
|
|
<td>purple</td>
|
|
<td>28</td>
|
|
</tr>
|
|
<tr>
|
|
<td>2</td>
|
|
<td>Apple</td>
|
|
<td>red</td>
|
|
<td>52</td>
|
|
</tr>
|
|
<tr>
|
|
<td>7</td>
|
|
<td>Cranberry</td>
|
|
<td>red</td>
|
|
<td>308</td>
|
|
</tr>
|
|
<tr>
|
|
<td>0</td>
|
|
<td>Banana</td>
|
|
<td>yellow</td>
|
|
<td>89</td>
|
|
</tr>
|
|
<tr>
|
|
<td>3</td>
|
|
<td>lemon</td>
|
|
<td>yellow</td>
|
|
<td>15</td>
|
|
</tr>
|
|
</table>
|
|
</div>
|
|
<aside>
|
|
<div class="bug-reporting__panel">
|
|
<h3>Find an error or bug? Have a suggestion?</h3>
|
|
<p>Everything on this site is avaliable on GitHub. Head on over and <a href='https://github.com/redoules/redoules.github.io/issues/new'>submit an issue.</a> You can also message me directly by <a href='mailto:guillaume.redoules@gadz.org'>email</a>.</p>
|
|
</div>
|
|
</aside>
|
|
</section>
|
|
|
|
</div>
|
|
<!-- start of footer section -->
|
|
<footer class="footer">
|
|
<div class="container">
|
|
<p class="text-muted">
|
|
<center>This project contains 119 pages and is available on <a href="https://github.com/redoules/redoules.github.io">GitHub</a>.
|
|
<br/>
|
|
Copyright © Guillaume Redoulès,
|
|
<time datetime="2018">2018</time>.
|
|
</center>
|
|
</p>
|
|
</div>
|
|
</footer>
|
|
|
|
<!-- This jQuery line finds any span that contains code highlighting classes and then selects the parent <pre> tag and adds a border. This is done as a workaround to visually distinguish the code inputs and outputs -->
|
|
<script>
|
|
$( ".hll, .n, .c, .err, .k, .o, .cm, .cp, .c1, .cs, .gd, .ge, .gr, .gh, .gi, .go, .gp, .gs, .gu, .gt, .kc, .kd, .kn, .kp, .kr, .kt, .m, .s, .na, .nb, .nc, .no, .nd, .ni, .ne, .nf, .nl, .nn, .nt, .nv, .ow, .w, .mf, .mh, .mi, .mo, .sb, .sc, .sd, .s2, .se, .sh, .si, .sx, .sr, .s1, .ss, .bp, .vc, .vg, .vi, .il" ).parent( "pre" ).css( "border", "1px solid #DEDEDE" );
|
|
</script>
|
|
|
|
|
|
<!-- Load Google Analytics -->
|
|
<script>
|
|
/*
|
|
(function(i, s, o, g, r, a, m) {
|
|
i['GoogleAnalyticsObject'] = r;
|
|
i[r] = i[r] || function() {
|
|
(i[r].q = i[r].q || []).push(arguments)
|
|
}, i[r].l = 1 * new Date();
|
|
a = s.createElement(o),
|
|
m = s.getElementsByTagName(o)[0];
|
|
a.async = 1;
|
|
a.src = g;
|
|
m.parentNode.insertBefore(a, m)
|
|
})(window, document, 'script', '//www.google-analytics.com/analytics.js', 'ga');
|
|
|
|
ga('create', 'UA-66582-32', 'auto');
|
|
ga('send', 'pageview');
|
|
*/
|
|
</script>
|
|
<!-- End of Google Analytics -->
|
|
|
|
<!-- Bootstrap core JavaScript
|
|
================================================== -->
|
|
<!-- Placed at the end of the document so the pages load faster -->
|
|
<script src="../theme/js/bootstrap.min.js"></script>
|
|
<!-- IE10 viewport hack for Surface/desktop Windows 8 bug -->
|
|
<script src="../theme/js/ie10-viewport-bug-workaround.js"></script>
|
|
|
|
|
|
</body>
|
|
|
|
</html> |