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!
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.
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
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.
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.
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.
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.
<sigh>
Borghunter’s idea was interesting.
I almost always learn something when I read these threads.