We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Medium Priority
644 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

Comment
Watch Question

Commented:
What is the warning message?
SharathData Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
chokkaStudent

Author

Commented:
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
chokkaStudent

Author

Commented:
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 ..!
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>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
chokkaStudent

Author

Commented:
asperkins, I didnt get the syntax ..

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

CERTIFIED EXPERT
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
chokkaStudent

Author

Commented:
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 ?
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>Scores are equally divided for Sarath and acperkins. <<
Sigh.
chokkaStudent

Author

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.