?
Solved

Internally looping and picking the Max value - using row_number() over by Partition

Posted on 2011-04-27
10
Medium Priority
?
560 Views
Last Modified: 2012-05-11
In the below query, i am selecting Max(Generic Code).

Problem with this query is resulting - Warning Message.

I am aware about row_number() over by Partition

I am not sure about the syntax, Expected result is to avoid duplicate rows and keep the max(Generic Code) row.
select		PC.ID,
		MAX(ISNULL(Dr.GenericCode,0)) GenericCode,
		PC.[Generic Name],
		PC.[NDC]
from		PlacedOrderDetails PC
Left Join	Drug Dr 
on convert(varchar, Left(PC.NDC ,9),101) = convert(varchar, Left(case when Dr.DrugNDCType = 49 then Dr.DrugNDCNbr else stuff(Dr.DrugNDCNbr,case when Dr.DrugNDCType in (50, 56) then 1 when Dr.DrugNDCType in (51, 57) then 6 when Dr.DrugNDCType = 52 then 10 end, 0, '0') end ,9),101)
Group by	PC.ID, PC.[Generic Name], PC.[NDC]
Order by	PC.ID ASC

Open in new window

0
Comment
Question by:chokka
10 Comments
 
LVL 2

Expert Comment

by:cojdev
ID: 35479867
What is the warning message?
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 1000 total points
ID: 35479936
try this.
SELECT * 
    FROM (SELECT PC.ID, 
                 PC.[Generic Name], 
                 PC.[NDC], 
                 Dr.GenericCode, 
                 ROW_NUMBER() 
                   OVER(PARTITION BY PC.ID,PC.[Generic Name],PC.[NDC] ORDER BY Dr.GenericCode DESC) rn
            FROM PlacedOrderDetails PC 
                 LEFT JOIN Drug Dr 
                   ON CONVERT(VARCHAR,LEFT(PC.NDC,9),101) = CONVERT(VARCHAR,LEFT(CASE 
                                                                                   WHEN Dr.DrugNDCType = 49 THEN Dr.DrugNDCNbr
                                                                                   ELSE STUFF(Dr.DrugNDCNbr,CASE
                                                                                                              WHEN Dr.DrugNDCType IN (50,56) THEN 1
                                                                                                              WHEN Dr.DrugNDCType IN (51,57) THEN 6
                                                                                                              WHEN Dr.DrugNDCType = 52 THEN 10
                                                                                                            END,0,'0')
                                                                                 END,9),101)) t1
   WHERE rn = 1 
ORDER BY ID ASC

Open in new window

0
 

Author Comment

by:chokka
ID: 35492108
Thank you sarath.

When i comment these two lines .. in order to avoid rn column - I am getting duplicate rows. Is there any way to avoid rn column ..! if not its okay !!

--,ROW_NUMBER() OVER(PARTITION BY PC.ID,PC.[Generic Name],PC.[NDC] ORDER BY Dr.GenericCode DESC) rn

 --WHERE rn = 1
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:chokka
ID: 35492715
Sarath   Comment ID: 35479936  was perfect.

It would be helpful to understand the syntax. If Sarath can explain us, it will be helpful for me as well as for others ..!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35493134
>>Is there any way to avoid rn column <<
All you have to do (which you should be doing in any case) is change  (no points please):
SELECT *
To:
SELECT ID, [Generic Name], [NDC], GenericCode
0
 

Author Comment

by:chokka
ID: 35493180
asperkins, I didnt get the syntax ..

SELECT *
To:
SELECT ID, [Generic Name], [NDC], GenericCode

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 35493214
I did not think I had to spell it out, but here goes (remember no points please):
SELECT ID, [Generic Name], [NDC], GenericCode
FROM (SELECT PC.ID, 
             PC.[Generic Name], 
             PC.[NDC], 
             Dr.GenericCode, 
             ROW_NUMBER() OVER(PARTITION BY PC.ID,PC.[Generic Name],PC.[NDC] ORDER BY Dr.GenericCode DESC) rn
       FROM  PlacedOrderDetails PC 
             LEFT JOIN Drug Dr ON CONVERT(VARCHAR,LEFT(PC.NDC,9),101) = CONVERT(VARCHAR,LEFT(CASE 
                               WHEN Dr.DrugNDCType = 49 THEN Dr.DrugNDCNbr
                               ELSE STUFF(Dr.DrugNDCNbr,CASE
                               WHEN Dr.DrugNDCType IN (50,56) THEN 1
                               WHEN Dr.DrugNDCType IN (51,57) THEN 6
                               WHEN Dr.DrugNDCType = 52 THEN 10
                           END,0,'0')
                       END,9),101)) t1
WHERE rn = 1 
ORDER BY ID ASC

Open in new window

0
 

Author Comment

by:chokka
ID: 35493232
I am sorry for that, I was not able to understand on your ID: 35493134
Thank you for your help. Scores are equally divided for Sarath and acperkins. Both of you helped me a lot.


I wish to learn this logic concept and do it myself. Can you please explain about this syntax ?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35493395
>>Scores are equally divided for Sarath and acperkins. <<
Sigh.
0
 

Author Comment

by:chokka
ID: 35493423
acperkins, i am not able to understand your short quote comments. It will be great, if you can explain about the syntax and how to implement this syntax in such scenarios
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 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