Thread: Problem of Null Ordering

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  ( ^_^ )

Harry Yau wrote:

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.

All you need to do is order your results by IF1. Use:

select IF1 from TABLE_NAME order by IF1 desc;

Jc

PS Don't crosspost to other postgres groups. It's very rude. Pick the
group that best first your particular need and post to only that group.

On Tue, 3 Dec 2002, Harry Yau wrote:

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

I don't know of such a setting. I have however a workaround that you can
apply to the order by clause. If b is the column that contains NULL values
that you want to order by, then you can instead order in this way:

ORDER BY b IS NULL DESC, b

(or ORDER BY b IS NOT NULL, b)

which will give you the NULL values first. It's probably not standard SQL.

ps. Why post to several lists?

--
/Dennis

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
 
 

I often use this trick:

SELECT * FROM t1 ORDER BY (IF1 IS NOT NULL), if1;

The first order is by a boolean value, and false comes first. Effectively,
it's the same thought as the union. But if you fear boolean expressions, try
this:

SELECT * FROM t1 ORDER BY (CASE WHEN if1 IS NULL THEN 0 ELSE 1 END), if1;

HTH,

G.
--
while (!asleep()) sheep++;

---------------------------- cut here ------------------------------
----- Original Message -----
From: "Harry Yau" <harry.yau@regaltronic.com>
Sent: Tuesday, December 03, 2002 10:54 AM


> 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