snipt

Ctrl+h for KB shortcuts

MySQL

Specify 'ORDER BY' order

1
2
3
// you would use something like this to specify the order in which you want the results returned

SELECT * FROM tablename ORDER BY col='text1' desc, col='text2' desc, col='text3' desc
https://snipt.net/embed/41bc7f9ad7a6d63347e267cc355fa771/
/raw/41bc7f9ad7a6d63347e267cc355fa771/
41bc7f9ad7a6d63347e267cc355fa771
mysql
MySQL
3
2019-05-27T00:28:01
True
False
False
/api/public/snipt/14229/
specify-order-by-order
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="o">//</span> <span class="n">you</span> <span class="n">would</span> <span class="k">use</span> <span class="n">something</span> <span class="k">like</span> <span class="n">this</span> <span class="k">to</span> <span class="n">specify</span> <span class="n">the</span> <span class="k">order</span> <span class="k">in</span> <span class="n">which</span> <span class="n">you</span> <span class="n">want</span> <span class="n">the</span> <span class="n">results</span> <span class="n">returned</span> </span><span id="L-2"><a name="L-2"></a> </span><span id="L-3"><a name="L-3"></a><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">tablename</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">col</span><span class="o">=</span><span class="s1">&#39;text1&#39;</span> <span class="k">desc</span><span class="p">,</span> <span class="n">col</span><span class="o">=</span><span class="s1">&#39;text2&#39;</span> <span class="k">desc</span><span class="p">,</span> <span class="n">col</span><span class="o">=</span><span class="s1">&#39;text3&#39;</span> <span class="k">desc</span> </span></pre></div> </td></tr></table>
"order by", mysql, sorting, sql

MySQL

Calculate the difference between dates in on column within the same table

1
2
3
4
SELECT DATEDIFF(
 (SELECT DATE(date_field) FROM table WHERE id = '1' AND status_id = '2' ORDER BY date_field DESC LIMIT 1),
 (SELECT DATE(date_field) FROM table WHERE id = '1' AND status_id = '1' ORDER BY date_field DESC LIMIT 1)
	) AS difference
https://snipt.net/embed/92c1f8bbeb80a7a6033d40cae85dcd24/
/raw/92c1f8bbeb80a7a6033d40cae85dcd24/
92c1f8bbeb80a7a6033d40cae85dcd24
mysql
MySQL
4
2019-06-15T13:23:34
True
False
False
/api/public/snipt/14227/
calculate-the-difference-between-dates-in-one-columns-within-the-same-table
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">SELECT</span> <span class="nf">DATEDIFF</span><span class="p">(</span> </span><span id="L-2"><a name="L-2"></a> <span class="p">(</span><span class="k">SELECT</span> <span class="kt">DATE</span><span class="p">(</span><span class="n">date_field</span><span class="p">)</span> <span class="k">FROM</span> <span class="k">table</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="s1">&#39;1&#39;</span> <span class="k">AND</span> <span class="n">status_id</span> <span class="o">=</span> <span class="s1">&#39;2&#39;</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">date_field</span> <span class="k">DESC</span> <span class="k">LIMIT</span> <span class="mi">1</span><span class="p">),</span> </span><span id="L-3"><a name="L-3"></a> <span class="p">(</span><span class="k">SELECT</span> <span class="kt">DATE</span><span class="p">(</span><span class="n">date_field</span><span class="p">)</span> <span class="k">FROM</span> <span class="k">table</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="s1">&#39;1&#39;</span> <span class="k">AND</span> <span class="n">status_id</span> <span class="o">=</span> <span class="s1">&#39;1&#39;</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">date_field</span> <span class="k">DESC</span> <span class="k">LIMIT</span> <span class="mi">1</span><span class="p">)</span> </span><span id="L-4"><a name="L-4"></a> <span class="p">)</span> <span class="k">AS</span> <span class="n">difference</span> </span></pre></div> </td></tr></table>
change, date, datediff, difference, mysql, status, subquery