April 8, 2011 at 10:37 am
Hi All,
CREATE TABLE #temptable(tranName varchar(1),planname varchar(20), priority int)
INSERT INTO #temptable VALUES ('a','plan1',1)
INSERT INTO #temptable VALUES ('a','plan2',2)
INSERT INTO #temptable VALUES ('c','plan3',3)
INSERT INTO #temptable VALUES ('c','plan4',4)
My result set needs to be:
aplan1
cplan3
I need distinct tranName with planNames for which the prority value is the minimum. In the real time environment the tranname column is actually a set of columns that make up a transaction.
The solution I followed is below:
SELECT tranName,(SELECT planName FROM #temptable t1 WHERE t1.priority=MIN(t2.priority)) from #temptable t2 GROUP BY a
Please let me know if there is a better solution.
Thanks and Regards,
Praveena.
April 8, 2011 at 12:15 pm
SELECT
t.tranname,
planname
FROM #temptable t INNER JOIN
(SELECT
tranname,
MIN(priority) as min_priority
FROM #temptable
GROUP BY tranName) t1
ON t.tranName = t1.tranName AND t.priority = t1.min_priority
April 8, 2011 at 12:20 pm
Keep going...your query is subject to a data-driven bug.
CREATE TABLE #temptable(tranName varchar(1),planname varchar(20), priority int)
INSERT INTO #temptable VALUES ('a','plan1',1)
INSERT INTO #temptable VALUES ('a','plan2',2)
INSERT INTO #temptable VALUES ('c','plan3',3)
INSERT INTO #temptable VALUES ('c','plan4',4)
-- note: this row breaks your query with error "Subquery returned more than 1 value"
INSERT INTO #temptable VALUES ('c','plan2',2)
SELECT t2.tranName,
(SELECT t1.planName
FROM #temptable t1
WHERE t1.priority = MIN(t2.priority)
)
FROM #temptable t2
GROUP BY t2.tranName
Try something like this:
WITH cte(tranName, planname, row_num)
AS (SELECT tranName,
planname,
ROW_NUMBER() OVER (PARTITION BY tranName ORDER BY priority) row_num
FROM #temptable
)
SELECT tranName,
planname
FROM cte
WHERE row_num = 1 ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 8, 2011 at 2:29 pm
Thank you for the replies!
@ opc.three: This solution worked for me. Thank you. In my case I have a almost 10 columns which make a transaction. So, I am trying something like the below code:
;WITH cte(tranName1, tranName2, tranName3, tranName4, planname, row_num)
AS (SELECT tranName1,tranName2, tranName3, tranName4, planname,
ROW_NUMBER() OVER (PARTITION BY tranName1,tranName2,tranName3, tranName4 ORDER BY priority) row_num
FROM #temptable
)
SELECT tranName1, tranName2, tranName3, tranName4, planname
FROM cte
WHERE row_num = 1 ;
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply