You must've been reading my mind. I was just wondering what to do about indexing on that particular table. I read somewhere that an Index is not going to improve the performance of an ORDER BY if the sort column contains NULLs because NULLs aren't indexed?
For the sake of the example I had simplified matters a wee bit. What I really have is:
SELECT * FROM PRODUCT ORDER BY NAME, FROM, TO, FROM2, TO2
FROM, TO, FROM2, TO2 might be NULL. If FROM is NULL, TO will be NULL. If FROM2 is NULL, TO2 will be NULL.
How would you index this table?
Kind regards, Tarlika
Jean-Luc Lachance wrote:
select ... order by "FROM" is not null, "FROM";
If you have large amount of rows (with or without nulls) it is faster if use a partial index.
create index ... on ...("FROM"); create index ... on ...("FROM") where "FROM" is null;
JLL
[EMAIL PROTECTED] wrote:
Use the coalesce() function. (coalesce returns the first non-null value in its list)
Specifically
ORDER BY coalesce("TO", 0), "FROM"
If you have records in "TO" column whose values is LESS then 0, then you need to replace 0 with
something that sorts BEFORE the first most value that your TO result can return.
Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of T E Schmitz Sent: Sunday, September 19, 2004 10:58 AM To: [EMAIL PROTECTED] Subject: [SQL] ORDER BY and NULLs
Hello,
I am using PostgreSQL 7.4.2 and as I understand NULL values always sort last.
However, I have a table from which select using two numerical sort keys "FROM" and "TO". "TO" might be NULL and I would like to display those rows first (without sorting the column in descending order).
Is there any way this can be achieved without inserting bogus values into that column?
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
