[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

select id if specified otherwise select min from group

Posted on 2011-05-11
4
Medium Priority
?
382 Views
Last Modified: 2012-05-11
Hi experts

I have a table with this data:

ID - GroupID - Price
-------------------------------
1 - 1 - 11
2 - 1 - 12
3 - 1 - 13
4 - 2 - 101
5 - 2 - 102
6 - 2 - 103

Prices are just random prices

I need a SP where i can send fx 2, 5 or 2and5
If I send 2, i like to retrieve ID2 (specified) and id 4 (lowest price with groupID 2)
If I send 5, i like to retrieve ID5 (specified) and id 1 (lowest price with groupID 1)
If I send 2 ad 5 i need ID2+5 returned, and finally, if i send nothing, ID1+4 (lowest in each group should be returned)

 There can be n in each group, and there can be n groups

Any ideas??
0
Comment
Question by:krogstruphede
  • 2
4 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 35737114
really confusing...

"fx 2, 5 or 2and5" : what does this mean? and for these statements I could not get the logic

If I send 2, i like to retrieve ID2 (specified) and id 4 (lowest price with groupID 2)
If I send 5, i like to retrieve ID5 (specified) and id 1 (lowest price with groupID 1)
If I send 2 ad 5 i need ID2+5 returned, and finally, if i send nothing, ID1+4 (lowest in each group should be returned)
0
 
LVL 15

Accepted Solution

by:
tim_cs earned 1000 total points
ID: 35737171
Try this and see if it gives you what you're looking for.  
CREATE PROCEDURE procName
	-- Add the parameters for the stored procedure here
	@id1 int = null
	@id2 int = null
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	;WITH CTE1 AS (
		SELECT
			*
		FROM
			tableName A
		WHERE
			A.ID = @id1
			OR A.ID = @id2
	),
	CTE2 AS (
		SELECT
			ID
			,GroupID
			,Price
			,ROW_NUMBER() OVER (Partition By GroupID ORDER BY Price) RN
		FROM
			tableName A
			LEFT JOIN CTE1 B
				ON A.GroupID = B.GroupID
		WHERE
			B.ID IS NULL
	)


	SELECT
		*
	FROM
		CTE1
	UNION ALL
	SELECT
		*
	FROM	
		CTE2
	WHERE
		RN = 1
END
GO

Open in new window

0
 
LVL 15

Expert Comment

by:tim_cs
ID: 35737202
Ooops, left off a comma after the first parameter. It should be....

@id1 int = null
,@id2 int = null

0
 

Author Comment

by:krogstruphede
ID: 35737895
My sample data, is actually a collection from several tables, so using two cte's would give poorly performance.
However the idea with rownumber, got me in right direction.

I solved it like this: (given that cte also returns rownumber partitioned by groupid)

with cte as (select with a lot of joins = datasample + rownumber)
select * from cte where
id in (_parameters_) or
(rownumber = 1 and groupid not in (select groupid from cte where id in (_parameters_)))

I havn't tested it, but I would assume that this way would give better perfomance, rather than using 2 cte's and an union!?

Thx for the quick answer
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

What we learned in Webroot's webinar on multi-vector protection.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

834 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