# SQL help (ordering search results) **URL:** \ **Category:** Factual Questions\ **Created:** [October 20, 2010, 10:43pm UTC](https://boards.straightdope.com/t/sql-help-ordering-search-results/557831 "2010-10-20T22:43:23Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Gary\_Robson](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/gary_robson/32/3448_2.png) [@Gary\_Robson](https://boards.straightdope.com/u/Gary_Robson)\ **Post date:** [October 20, 2010, 10:43pm UTC](https://boards.straightdope.com/t/sql-help-ordering-search-results/557831/1 "2010-10-20T22:43:23Z")
I’ve been using databases for a long time, but I’ve only recently taught myself SQL. I’ve built a relational database of book data, and my sort results are wonky. There are two fields involved: title (varchar 120) and series (varchar 30). Both use utf8\_general\_ci. The title field is required. The series field is optional. If I query "SELECT \* FROM `books` ORDER BY `title`" I get precisely what I expect: all books in the database in alphabetical order by title. If I query "SELECT \* FROM `books` ORDER BY `series`, `title`" I get all of the series grouped as expected, but when the series field is blank, the books appear to be in random order (the order in which they were added to the database, perhaps?). What am I doing wrong? This is driving me nuts! ---
**Author:** ![Kinthalis](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kinthalis/32/16084_2.png) [@Kinthalis](https://boards.straightdope.com/u/Kinthalis)\ **Post date:** [October 20, 2010, 10:50pm UTC](https://boards.straightdope.com/t/sql-help-ordering-search-results/557831/2 "2010-10-20T22:50:46Z")
There’s a difference between a blank and a null and SQL will treat them differently. Maybe a SELECT CASE where you are checking to see if series is null and then sort by title only? You cna also not allow nulls in series, and insert a default of NONE. ---
**Author:** ![BorgHunter](https://avatars.discourse-cdn.com/v4/letter/b/7c8e57/32.png) [@BorgHunter](https://boards.straightdope.com/u/BorgHunter)\ **Post date:** [October 20, 2010, 10:52pm UTC](https://boards.straightdope.com/t/sql-help-ordering-search-results/557831/3 "2010-10-20T22:52:13Z")
I’m too lazy to actually try this out, but maybe something like this would work? SELECT \* FROM books WHERE series IS NOT NULL ORDER BY series, title UNION SELECT \* FROM books WHERE series IS NULL ORDER BY title ---
**Author:** ![goldmund](https://avatars.discourse-cdn.com/v4/letter/g/a4c791/32.png) [@goldmund](https://boards.straightdope.com/u/goldmund)\ **Post date:** [October 21, 2010, 1:59am UTC](https://boards.straightdope.com/t/sql-help-ordering-search-results/557831/4 "2010-10-21T01:59:09Z")
I’ll guess you aren’t using MySQL, as it behaves like you expected. If you’re using MS SQL server, or Oracle, you could use the isnull() or nvl() functions, respectively. These take two arguments, the first is a value, the second is a string to replace the value with, if the value is null. So, MS SQL example: select \* from books order by isnull(series, ‘’), title; This will replace any null values for the ‘series’ column with an empty string (an empty string being different from a NULL value), which should source properly. The nvl() in Oracle works the same way. ---
**Author:** ![Gary\_Robson](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/gary_robson/32/3448_2.png) [@Gary\_Robson](https://boards.straightdope.com/u/Gary_Robson)\ **Post date:** [October 21, 2010, 12:53pm UTC](https://boards.straightdope.com/t/sql-help-ordering-search-results/557831/5 "2010-10-21T12:53:42Z")
I am, indeed, using MySQL. That’s what has me so puzzled. I’m going to experiment a bit with **BorgHunter** ’s idea and report back. ---
**Author:** ![Gary\_Robson](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/gary_robson/32/3448_2.png) [@Gary\_Robson](https://boards.straightdope.com/u/Gary_Robson)\ **Post date:** [October 21, 2010, 1:10pm UTC](https://boards.straightdope.com/t/sql-help-ordering-search-results/557831/6 "2010-10-21T13:10:23Z")
I figured out the problem, and it was indeed, NULL related. The “series” field allows NULL and has a default of NULL. My input/editing forms, however, all write that field, and if the user inputs nothing, they submit an empty string as the value. When I was initially testing my system, I entered some data manually using the interactive database editor provided by my web host. There, I didn’t put anything in the “series” field, and so those records had NULL. I edited each of those entries using my forms, and now everything is consistent and sorting properly. Thanks, all, for pointing me in the right direction. ---
**Author:** ![Gagundathar](https://avatars.discourse-cdn.com/v4/letter/g/a183cd/32.png) [@Gagundathar](https://boards.straightdope.com/u/Gagundathar)\ **Post date:** [October 21, 2010, 1:13pm UTC](https://boards.straightdope.com/t/sql-help-ordering-search-results/557831/7 "2010-10-21T13:13:11Z")
Late to the party as usual. This is actually one of the things I am supposed to be good at (since part of what I do for money involves intimate knowledge of MSSQL) and I was eager to help. \ **Borghunter** ’s idea was interesting. I almost always learn something when I read these threads.