Re: [GENERAL] Problem of Null Ordering

Jean-Christian Imbeault wrote:

> 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.

>
> All you need to do is order your results by IF1. Use:
>
> select IF1 from TABLE_NAME order by IF1 desc;
>
> Jc

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.
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