Re: Help - Help on SQL
Kalyan,
I think this is one way to achieve what you want, use a
constant as your sort key and then do a union of 4 selects.
I have not tested this code but something like this should
work.
select col1, col2, col3 , 1 sort_key from tbl1
where col1 is not NULL
and col2 is not NULL
and col3 is not NULL
union
select col1, col2, col3 , 2 sort_key from tbl1
where col1 is NULL
and col2 is not NULL
and col3 is not NULL
union
select col1, col2, col3 , 3 sort_key from tbl1
where col1 is NULL
and col2 is NULL
and col3 is not NULL
union
select col1, col2, col3 , 4 sort_key from tbl1
where col1 is NULL
and col2 is NULL
and col3 is NULL
ORDER BY sort_key, col1, col2, col3
Regards -- Lester
} I got a couple of replies for my question below which had asked
} me to do a DESC order. I am sorry I did'nt write my question
} properly. I want the resultant set to be ordered ASC but the null
} values ( should in some way assume the highest value ) should
} appear at the bottom.
}
} If you take a look at the resultant set. It is in ASC order the
} only difference being that the NULL values have taken on the
} highest values and appears at the bottom.
}
} Sorry for being vague when I asked my question.Thanks for the
} responses.
}
} Thanks,
} Kalyan.
} ______________________________ Forward Header __________________________________
} Subject: Help on SQL
} Author: T. Kalyan at North-Supply
} Date: 12/18/95 2:34 PM
}
}
} The result set we get for ...
}
} SELECT col1, col2, col3 FROM tbl1 ORDER BY col1, col2, col3
}
} ... is as shown below :
}
}
} col1 col2 col3
} ^^^^ ^^^^ ^^^^
} Null Null A
} Null Null B
} Null A A
} Null A B
} Null B A
} A A A
} A A B
} A B A
}
} Is it possible to write a SQL so that the NULL values are pushed
} to the bottom and the ordering remains the same.
}
} The resultant set should be as shown below.
}
} col1 col2 col3
} ^^^^ ^^^^ ^^^^
} A A A
} A A B
} A B A
} Null A A
} Null A B
} Null B A
} Null Null A
} Null Null B
}
} Thanks,
} Kalyan.
}
#############################################################################
# Lester Knutsen lester@access.digex.net #
# Advanced DataTools Corporation Voice: 703-256-0267 #
# Grant group privileges for Informix databases with DB Privileges #
# Visit our Web page: http://www.access.digex.net/~lester #
#############################################################################