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