Problem of Null Ordering
Problem of Null Ordering
От:
Harry Yau <harry.yau@regaltronic.com>
Дата:
Hi all, I got some problem with the ordering. I have a table which have a integer field , namely IF1, which is able to have NULL value. When I query this table and Order by IF1, the row that have the value of NULL will come after the row that have a number value. I am wondering is there anything I can do to make the row with Null value come before the row with a number value. (Same as other RDBMS does - Sybase, MYSQL etc.) Be more specified, I want to do something in the DBMS level instead of doing some union queries. And apply this standard to all other query and table in PGSQL. Thank In Advance. Harry Yau ( ^_^ )
Re: [GENERAL] Problem of Null Ordering
От:
Harry Yau <harry.yau@regaltronic.com>
Дата:
Jean-Christian Imbeault wrote:
For Example,
I have the following table and data.
T1:
Harry Yau wrote:
>
> Hi all,
>I got some problem with the ordering.
>I have a table which have a integer field , namely IF1, which is able to have NULL value.
>When I query this table and Order by IF1, the row that have the value of
>NULL will come after the row that have a number value. I am wondering is
>there anything I can do to make the row with Null value come before the
>row with a number value. (Same as other RDBMS does - Sybase, MYSQL etc.)
>Be more specified, I want to do something in the DBMS level instead of
>doing some union queries. And apply this standard to all other query and
>table in PGSQL.
>Thank In Advance.
Sorry About this. I think I should have clarified this. What I want is that Null valued Rows come first then rows with number come after it in the result. Moreover the rows with number are also sorted in a ascending order.
All you need to do is order your results by IF1. Use:select IF1 from TABLE_NAME order by IF1 desc;
Jc
For Example,
I have the following table and data.
T1:
| IF1 | SF1 |
| 1 | A |
| 5 | B |
| 4 | C |
| 2 | D |
| NULL | E |
| 3 | F |
After the query, I wanna something look like:
| IF1 | SF1 |
| NULL | E |
| 1 | A |
| 2 | D |
| 3 | F |
| 4 | C |
| 5 | B |
But with Jean-Christian suggestion, result will be something like:
| IF1 | SF1 |
| NULL | E |
| 5 | B |
| 4 | C |
| 3 | F |
| 2 | D |
| 1 | A |
I know that it is possible to do this query. Such as
SELECT *, 0 AS PreOrder FROM T1 WHERE IF1 IS NULL
UNION ALL
SELECT *, 1 AS PreOrder FROM T1 WHERE IF1 IS NOT NULL
ORDER BY PreOrder, IF1
The above query will give the order that I wanted. However, I am wondering is there any change or modification I can mark to the PGSQL to change the prioity of Null in SORTING for the rest of my life. ( At least within my own machine.)
Thank You Very Much!
Harry Yau