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

order by and UNION

Hi, I got an error "ORDER BY items must appear in the select list if the
statement contains a UNION operator." when trying to put an order clause at
the end ot the union query.
How to set order to the whole set? Thanks.That will order the entire set, if the column exists. Does it? Can you
post the code you're having trouble with?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"js" <js@.someone@.hotmail.com> wrote in message
news:%23FAx7z1IFHA.1248@.TK2MSFTNGP10.phx.gbl...
> Hi, I got an error "ORDER BY items must appear in the select list if the
> statement contains a UNION operator." when trying to put an order clause
at
> the end ot the union query.
> How to set order to the whole set? Thanks.
>|||When using the ORDER BY clause in a query with a UNION, you must adhere
to the ANSI SQL-92 standard, which says you cannot use expressions in
the ORDER BY clause, but only the names or ordinal positions of the
columns in the resultset.
Hope this helps,
Gert-Jan
js wrote:
> Hi, I got an error "ORDER BY items must appear in the select list if the
> statement contains a UNION operator." when trying to put an order clause a
t
> the end ot the union query.
> How to set order to the whole set? Thanks.|||select colA,colB
from tableA
union
select stuff as colA,blah as ColB
from tableB
order by 1,2 asc
// 1 = colA and 2=colB
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"js" <js@.someone@.hotmail.com> wrote in message
news:%23FAx7z1IFHA.1248@.TK2MSFTNGP10.phx.gbl...
> Hi, I got an error "ORDER BY items must appear in the select list if the
> statement contains a UNION operator." when trying to put an order clause
> at the end ot the union query.
> How to set order to the whole set? Thanks.
>