Re: Sorting empty rows at the bottom of a recordset

On Sat, Dec 10, 2005 at 10:10:27AM +0800, Matt Arnilo S. Baluyos (Mailing Lists) wrote:
> I have a recordset that returns rows based on a column (ORDER BY
> writer_lname, i.e. writer's last names). There are however rows which
> have empty values and these get sorted at the top of the recordset.
> What the boss would want to see is these rows to be sorted at the
> bottom of the recordset.

By "empty" do you mean NULL, or are the values zero-length strings
or strings that consist of only whitespace?  Since PostgreSQL 7.2
NULL comes after non-NULL in ascending sorts, so I'd guess that
either the empty strings are non-NULL or that you're using an ancient
version of PostgreSQL.

If the empty strings are zero-length but not NULL then you could
do this:

ORDER BY length(writer_lname) = 0, writer_lname

This relies on the behavior that FALSE sorts before TRUE, so strings
whose lengths are not zero will come first.  If that's not obvious
enough then you could use a CASE expression:

ORDER BY CASE length(writer_lname) WHEN 0 THEN 1 ELSE 0 END, writer_lname

Maybe somebody else will post a better idea.

Another possibility would be to convert empty strings to NULL, if
that makes sense to your application, and rely on NULL sorting after
non-NULL.

--
Michael Fuhr