Problem of Null Ordering

Поиск
Список
Период
Сортировка
Искать

Problem of Null Ordering

От:
Harry Yau <harry.yau@regaltronic.com>
Дата:
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  ( ^_^ )



Re: [GENERAL] Problem of Null Ordering

От:
Harry Yau <harry.yau@regaltronic.com>
Дата:
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:
IF1SF1
1A
5B
4C
2D
NULLE
3F

After the query, I wanna something look like:

IF1SF1
NULLE
1A
2D
3F
4C
5B

But with Jean-Christian suggestion, result will be something like:

IF1SF1
NULLE
5B
4C
3F
2D
1A

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
 
 

FAQ