Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2008
    Posts
    6

    Unanswered: Listing most commented posts by descending order

    Hi,

    I have 2 simple tables named "TblPosts" and "TblComments" and i want to list most commented posts by descending order.

    TblPosts

    id | subject | detail |
    1 | test | test |

    TblComments

    id | comment | post_id |
    1 | test | 1 |

    i think i have to use discint or group by but i didn't find how my sql query must be .

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT TblPostsTblTable.id as TblPostsTblTable_id 
         , COUNT(TblCommentsTblTable.id) AS TblCommentsTblTable_comments
      FROM TblPosts AS TblPostsTblTable
    LEFT OUTER
      JOIN TblComments AS TblCommentsTblTable
        ON TblCommentsTblTable.post_id = TblPostsTblTable.id
    GROUP
        BY TblPostsTblTable_id
    ORDER
        BY TblCommentsTblTable_comments DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2008
    Posts
    6
    thank you very much but i got this error in query manager

    Msg 207, Level 16, State 1, Line 8
    Invalid column name 'TblPostsTblTable_id'.
    -------

    i changed 'TblPostsTblTable_id' to 'TblPostsTblTable.id' and its now okey
    Last edited by izmarit; 04-11-09 at 10:52.

Posting Permissions

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