{"id":6543,"date":"2020-12-15T09:00:00","date_gmt":"2020-12-15T09:00:00","guid":{"rendered":"https:\/\/www.bronco.co.uk\/our-ideas\/?p=6543"},"modified":"2023-10-11T17:07:52","modified_gmt":"2023-10-11T16:07:52","slug":"how-to-order-in-mysql-putting-zero-or-null-at-the-end","status":"publish","type":"post","link":"https:\/\/www.bronco.co.uk\/our-ideas\/how-to-order-in-mysql-putting-zero-or-null-at-the-end\/","title":{"rendered":"How to Order in MySQL, putting Zero or NULL at the end"},"content":{"rendered":"\n<p>You may find yourself in a situation where you want to order a certain column in ascending order but have zero or NULL values at the end of the list rather than the start.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Zero<\/h2>\n\n\n\n<p>This happened to me where a client had certain products with no prices but didn&#8217;t want them showing up at the top of the list when a user ordered the products by price, low to high.<\/p>\n\n\n\n<p>The solution looks strange but is very straightforward; say your field is called &#8220;price&#8221;, you simply include <code>price = 0<\/code> in your <code>ORDER<\/code> clause, as follows:<\/p>\n\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-plain\"><code>ORDER BY price = 0, price ASC<\/code><\/pre><\/div>\n\n\n\n<p>The result is a list of products in ascending order based on their price as you&#8217;d expect, but with anything with a price of 0 at the end of the list.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">NULL<\/h2>\n\n\n\n<p>If your database field contains NULL values here&#8217;s another trick:<\/p>\n\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-plain\"><code>ORDER BY -price DESC<\/code><\/pre><\/div>\n\n\n\n<p>This is basically <code>price ASC<\/code>, so you get an ascending list of product prices as above, while putting NULL values at the end.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<p>This code is free to use at your own discretion. It comes without warranty. Please feel free to feedback any edits.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>You may find yourself in a situation where you want to order a certain column in ascending order but have zero or NULL values at the end of the list rather than the start. Zero This happened to me where a client had certain products with no prices but didn&#8217;t want them showing up at [&hellip;]<\/p>\n","protected":false},"author":12,"featured_media":6549,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"class_list":["post-6543","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-web-and-ux"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.bronco.co.uk\/our-ideas\/wp-json\/wp\/v2\/posts\/6543","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bronco.co.uk\/our-ideas\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bronco.co.uk\/our-ideas\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bronco.co.uk\/our-ideas\/wp-json\/wp\/v2\/users\/12"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bronco.co.uk\/our-ideas\/wp-json\/wp\/v2\/comments?post=6543"}],"version-history":[{"count":1,"href":"https:\/\/www.bronco.co.uk\/our-ideas\/wp-json\/wp\/v2\/posts\/6543\/revisions"}],"predecessor-version":[{"id":12176,"href":"https:\/\/www.bronco.co.uk\/our-ideas\/wp-json\/wp\/v2\/posts\/6543\/revisions\/12176"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.bronco.co.uk\/our-ideas\/wp-json\/wp\/v2\/media\/6549"}],"wp:attachment":[{"href":"https:\/\/www.bronco.co.uk\/our-ideas\/wp-json\/wp\/v2\/media?parent=6543"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bronco.co.uk\/our-ideas\/wp-json\/wp\/v2\/categories?post=6543"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}