• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 113
  • Last Modified:

Find record with minimum variable value; correlated subquery produces empty result set

I have a stored proc like this
ALTER PROCEDURE [dbo].[uspGetTopCrossSells] 
	@customerId int, @ProductCategory nvarchar (50),
	@Score float Output, @ClientKey nvarchar (50) Output, @Ranking int Output
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT Score, ClientKey, Ranking
	FROM CrossSellScore css, ItemCategory ic
	WHERE customerid = @customerid
       and score > 6.0
       and css.ItemCategoryId = ic.Id
       and description like @ProductCategory+'|%'
       and EndDate=(Select max(EndDate) from DatePeriod)
    Order By Score Desc
END

Open in new window


This produces a result set like this:
Score         ClientKey                 Ranking
9.17      Play Things|Other Plush      13
8.463      Play Things|Plush      54
8.308      Play Things|Bungee      69
8.301      Play Things|Other      70
8.19      Play Things|Unstuffies      86
8.011      Play Things|Cloth            100

I only want the first record, with the minimum Ranking. My efforts to modify the query have failed, along lines like adding a subquery

and Ranking = (Select MIN(Ranking) from CrossSellScore
             as C where C.CustomerId = css.CustomerId)

How do I get only the record w/ the lowest Ranking?
0
Mark Klein
Asked:
Mark Klein
1 Solution
 
Daniel Van Der WerkenIndependent ConsultantCommented:
Try this:
SELECT TOP 1
              M.Score,
              M.ClientKey,
              M.Ranking
FROM
(
      SELECT Score, ClientKey, Ranking
      FROM CrossSellScore css, ItemCategory ic
      WHERE customerid = @customerid
       and score > 6.0
       and css.ItemCategoryId = ic.Id
       and description like @ProductCategory+'|%'
       and EndDate=(Select max(EndDate) from DatePeriod)
) M
ORDER BY M.Ranking ASC
0
 
ste5anSenior DeveloperCommented:
E.g.

WITH Ordered AS (
	SELECT	Score, 
		ClientKey, 
		Ranking
		ROW_NUMBER() OVER ( Ranking ASC ) AS RN
	FROM	CrossSellScore css, 
		ItemCategory ic
	WHERE	customerid = @customerid
		AND score > 6.0
		AND css.ItemCategoryId = ic.Id
		AND description like @ProductCategory+'|%'
		AND EndDate = ( SELECT MAX(EndDate) FROM DatePeriod )
	)
	SELECT	Score, 
		ClientKey, 
		Ranking	
	FROM	Ordered O
	WHERE	O.RN = 1;

Open in new window

 

btw, please use for every column the appropriate table alias name. Also check whether you can use the JOIN syntax.
0
 
Mark KleinAuthor Commented:
Worked perfectly, except returned a second row w/ nulls.  Is that b/c the select uses 'score' instead of '@score=css.Score' etc.?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now