Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2005
    Posts
    55

    Unanswered: return variable name as part of select statement.

    hey all,

    I have the following query:

    Code:
    ALTER PROCEDURE [dbo].[sp_SelectMostRecentArticle]
    
    AS
    BEGIN
    
    	DECLARE @article_id INT
    	SELECT @article_id = (
    		SELECT TOP 1 article_id
    		FROM article
    		ORDER BY article_id DESC
    	)
    
    	DECLARE @comment_count INT 
    	SELECT @comment_count = (
    		SELECT COUNT(comment_id) 
    		FROM comment
    		JOIN article ON article_id = comment_article_id
    		GROUP BY article_id
    		HAVING article_id = @article_id
    	)
    		
    
    	SELECT TOP 1 article_id, article_author_id, 
    	article_title, article_body, article_post_date, 
    	article_edit_date, article_status, article_author_id
    	article_author_ip, author_display_name, 
    	category_id, category_name--, comment_count AS @comment_count
    
    	FROM article
    
    	JOIN author ON author_id = article_author_id
    	JOIN category ON category_id = article_category_id
    
    	GROUP BY article_id, article_title, article_body, article_post_date, 
    	article_edit_date, article_status, article_author_ip,article_author_id,
    	author_display_name, category_id, category_name
    
    	HAVING article_id = @article_id
    
    END
    GO
    as you can see, im trying to return a comment_count value, but the only way I can do this is by defining the variable.

    I have had to do it this way, because I cannot say COUNT(comment.comment_id) AS comment_count or it returns an error that it cant reference the comment.comment_id.

    But when change it to FROM article, comment; I get errors about the article_author_id and article_comment_id.

    And i cant add a join, because it would return the amount of rows of the comment...

    unless someone could help with what i Just decribed (as i would prefer to do it this way), how would i return the variable value as part of the select statement?

    Cheers

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    , comment_count AS @comment_count
    should be
    , @comment_count AS comment_count

    and you don't need the group by in the last query as there is no aggregate function in your select

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by pdreyer
    and you don't need the group by in the last query as there is no aggregate function in your select
    And the HAVING is also not needed. Simply use a regular WHERE expression (HAVING operates on GROUPs)

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you guys are going to crow about that stuff and not ask what the top 1 is all about?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Aug 2005
    Posts
    55
    hey guys,

    thanks for the replies. will give that a try. and yeah, forgot to remove the group by & having since changing the select function...

    also, the top 1 is selected that last entry, as i dont know the id of the last inputted record. unless there is a better way?

    Cheers, and thanks again!

  6. #6
    Join Date
    Feb 2007
    Posts
    62
    On your larger query tou are using TOP without an ORDER BY clause. You are not getting the 'last inputted record', you are getting one record at random, ususally determined by the query plan. It may be 'the last' or it may not be.

  7. #7
    Join Date
    Aug 2005
    Posts
    55
    Quote Originally Posted by LoztInSpace
    On your larger query tou are using TOP without an ORDER BY clause. You are not getting the 'last inputted record', you are getting one record at random, ususally determined by the query plan. It may be 'the last' or it may not be.
    ahh. i see now. I must have forgot to remove it after i copied and pasted from another very similar query.

    Cheers,

Posting Permissions

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