From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | "Matt Arnilo S(dot) Baluyos (Mailing Lists)" <matt(dot)baluyos(dot)lists(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Sorting empty rows at the bottom of a recordset |
Date: | 2005-12-10 03:12:24 |
Message-ID: | 20051210031224.GA18003@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Fabien SK | 2005-12-10 18:04:18 | Views an non-present column |
Previous Message | Matt Arnilo S. Baluyos (Mailing Lists) | 2005-12-10 02:10:27 | Sorting empty rows at the bottom of a recordset |