Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2015-02-22
3
Medium Priority
?
103 Views
Last Modified: 2015-02-22
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
Comment
Question by:Mark Klein
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 20

Accepted Solution

by:
Daniel Van Der Werken earned 2000 total points
ID: 40624148
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
 
LVL 35

Expert Comment

by:ste5an
ID: 40624176
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
 

Author Closing Comment

by:Mark Klein
ID: 40624193
Worked perfectly, except returned a second row w/ nulls.  Is that b/c the select uses 'score' instead of '@score=css.Score' etc.?
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

664 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question