question abut "order by" null fields

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

question abut "order by" null fields

Alessandro Vincelli-4
Hi

I have 2 table

table1:
        id
        column1


table2:
        id
        column2


I perform this query


SELECT column1, column2 FROM table1 LEFT JOIN table2 USING (id)
ORDER BY column1, column2

In the rows where column1 is not null postgres perform ORDER BY column1,
column2

Problem:
In the rows where Column1 is null, postgres don't perform  ORDER BY  on
the column2

Why?

Same behavior when:

SELECT column1, column2 FROM table1 LEFT JOIN table2 USING (id)
ORDER BY coalesce(column1,''), column2


Thanks in advance

--
-------------------------------
Alessandro Vincelli




---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [hidden email])
Reply | Threaded
Open this post in threaded view
|

Re: question abut "order by" null fields

Michael Fuhr
On Fri, Jul 01, 2005 at 11:21:06AM +0200, Alessandro Vincelli wrote:

>
> SELECT column1, column2 FROM table1 LEFT JOIN table2 USING (id)
> ORDER BY column1, column2
>
> In the rows where column1 is not null postgres perform ORDER BY column1,
> column2
>
> Problem:
> In the rows where Column1 is null, postgres don't perform  ORDER BY  on
> the column2

Please post a complete example: the SQL statements to create,
populate, and query the tables, as well as the results you expect
to see and the results you actually get.

What version of PostgreSQL are you running, and on what platform?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match