[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
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
Question by:krogstruphede
• 2
4 Comments

LVL 61

Expert Comment

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

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
``````
0

LVL 15

Expert Comment

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

@id1 int = null
,@id2 int = null

0

Author Comment

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

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…
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…
Suggested Courses
Course of the Month18 days, 14 hours left to enroll

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.