Order by query

I have a Comment Table where a comment can have a reply, if the comment is replied to I want the reply to appear under the comment.

Based on the Fields CommentID and Parent ID the parentID is the Comment and the Comment with a ParentID set too that comment is the answer.

How do I build this Query?ORDER BY COALESCE(ParentID,CommentID),CommentID|||Cool it worked took a while, had too set the ParentId to Null

Select * From Comment
ORDER BY COALESCE(iParentID,iCommentID),iCommentID

Thanks alot|||Were they from blanks to Nulls?
If so you could use

ORDER
BY Coalesce(NullIf(iParentID,''),iCommentID),iComment ID)|||eeewwww!!

under which circumstances do you expect iParentID to be equal to an empty string??

i would put the odds of iParentID being a CHAR or VARCHAR at 0.0000001%, i.e. squadoosh (http://en.wikipedia.org/wiki/Pardon_the_Interruption)

:cool:|||Your right is an Integer, I was inserting a Zero into the DB probably shouldn't have been.|||yes, zero is definitely wrong, and is a sign that you were probably not defining the foreign key

a foreign key of 0 would require a primary key of 0 to exist, and while you can theoretically define a pk of 0 (using SET IDENTITY_INSERT), this would mean you'd have a "dummy" row with a pk of 0 to act as the parent of all the real rows that have no parent

now let me pose the question: if you do go to the trouble of defining a "dummy" row with a pk of 0, what's the parentID value for that row?

:cool:|||Your completely right No value is NULL, 0 is a value with No parent