Re: 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