Results 1 to 8 of 8

Thread: Order by query

  1. #1
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Unanswered: 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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ORDER BY COALESCE(ParentID,CommentID),CommentID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Cool it worked took a while, had too set the ParentId to Null

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

    Thanks alot

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Were they from blanks to Nulls?
    If so you could use
    Code:
    ORDER
        BY Coalesce(NullIf(iParentID,''),iCommentID),iCommentID)
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Your right is an Integer, I was inserting a Zero into the DB probably shouldn't have been.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Your completely right No value is NULL, 0 is a value with No parent

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •