Re: [GENERAL] Problem of Null Ordering

From: Harry Yau <harry(dot)yau(at)regaltronic(dot)com>
To: Jean-Christian Imbeault <jc(at)mega-bucks(dot)co(dot)jp>, PGA <pgsql-admin(at)postgresql(dot)org>, PGG <pgsql-general(at)postgresql(dot)org>, PGH <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [GENERAL] Problem of Null Ordering
Date: 2002-12-03 09:54:13
Message-ID: 3DEC7F45.65064CE7@regaltronic.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message SZUCS Gábor 2002-12-03 10:39:33 Re: Problem of Null Ordering
Previous Message Dennis Björklund 2002-12-03 09:33:12 Re: Problem of Null Ordering

Browse pgsql-general by date

  From Date Subject
Next Message Jan Hartmann 2002-12-03 09:58:45 Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)
Previous Message Karel Zak 2002-12-03 09:45:41 Re: 7.4 Wishlist