Обсуждение: Sorting empty rows at the bottom of a recordset

Hello everyone,

I have a PHP application that gets its database from PostgreSQL. I'm
trying to figure out a way to get around an ORDER BY problem.

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.

Is there any way around this at the PostgreSQL level? I can definitely
put some code in the PHP (application) level but that would be too
inefficient I think.

Any pointers would be greatly appreciated.

Best regards,
Matt

--
Stand before it and there is no beginning.
Follow it and there is no end.
Stay with the ancient Tao,
Move with the present.

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