Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2007
    Posts
    15

    Unanswered: Sql Query for top 15 records, 1 per category

    Hi,

    Here is my current sql which outputs top 15 most emailed items. I am using SQL SERVER 2008, the sql will be in a stored procedure.
    Code:
    SELECT  TOP 15
    		PS.poem_id, 
    		PS.vote_average,  
    		PS.vote_count, 
    		P.poem_title, 
    		P.name,
    		P.author_id,
    		P.bio,
    		P.comments, 
    		P.poem,
    		C.main_category,
    		C.subcategories,
    		C.h1,
    		C.url 
    	FROM poems AS P INNER JOIN 
    			poem_stats AS PS ON PS.poem_id = P.poem_id INNER JOIN 
    				category AS C ON P.category_fk = C.category_id  
    	WHERE P.publish_ind='Y' 
    	ORDER BY 
    		PS.emailed DESC, 
    		PS.vote_average DESC
    I want instead to output the top 1 most emailed item from each of the 15 main categories. In other words I want each category represented. How do I do this?

    Here is the sql to output the main categories
    Code:
    SELECT DISTINCT main_category FROM category
    Thanks a lot!
    Last edited by aish1108; 02-16-11 at 11:51. Reason: changed IP to P

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try
    Code:
    with CTE(poem_id, vote_average, vote_count, poem_title, name, 
    	author_id, bio, comments, poem, main_category,
    	subcategories, h1, url, RowNum)
    AS 
    (	SELECT  
    		PS.poem_id, 
    		PS.vote_average,  
    		PS.vote_count, 
    		P.poem_title, 
    		P.name,
    		P.author_id,
    		P.bio,
    		P.comments, 
    		P.poem,
    		C.main_category,
    		C.subcategories,
    		C.h1,
    		C.url,
    		ROW_NUMBER() OVER (PARTITION BY C.main_category ORDER BY C.main_category, PS.poem_id) AS RowNum
    	FROM poems AS P 
    		INNER JOIN poem_stats AS PS ON 
    			PS.poem_id = P.poem_id 
    		INNER JOIN category AS C ON 
    			IP.category_fk = C.category_id  
    	WHERE P.publish_ind='Y' 
    )
    
    SELECT *
    FROM CTE
    WHERE RowNum = 1
    ORDER BY main_category DESC
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Aug 2007
    Posts
    15
    That does the trick. Thanks. I've never used PARTITION before.

Posting Permissions

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