Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Retrieving a NULL when using the Max Aggregate
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stevenandler
Starting Member

USA
42 Posts

Posted - 04/30/2013 :  11:18:23  Show Profile  Reply with Quote
I have a following script which returns all the records with the max NPI number. The problem is if there is no NPI number, I still want the T-SQL script to return one row with the doctor's information. here is my T_SQL script:

SELECT A.NPI AS NPI,A.DR_FULL_NAME AS DR, A.ADDRESS_1 AS ADDRESS,A.CITY AS CITY, A.ZIP AS ZIP
FROM OGEN.GEN_M_DOCTOR_MAST A
INNER JOIN
(
SELECT MAX(NPI) AS LATEST_NPI,DR_FULL_NAME,ADDRESS_1,CITY,ZIP
FROM OGEN.GEN_M_DOCTOR_MAST
GROUP BY DR_FULL_NAME,ADDRESS_1,CITY,ZIP
) ABC
ON A.NPI = ABC.LATEST_NPI
WHERE A.NPI IS NULL
ORDER BY A.DR_FULL_NAME


Please suggest what I need to change or add to this script to also pick up a NULL or empty NPI.

Thank you.

ahmeds08
Aged Yak Warrior

India
737 Posts

Posted - 04/30/2013 :  11:34:22  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
replace MAX(NPI) with isnull(MAX(NPI),0).
this will display the null value as 0

mohammad.javeed.ahmed@gmail.com
Go to Top of Page

stevenandler
Starting Member

USA
42 Posts

Posted - 04/30/2013 :  11:48:04  Show Profile  Reply with Quote
Tried this but still doesn't work. I'm using SQL Server 2008 R2.
Any other suggestions?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 04/30/2013 :  11:50:08  Show Profile  Reply with Quote
You would also need to do that on the join condition
ON ISNULL(A.NPI,0) = ABC.LATEST_NPI

Another alternative is this:
SELECT NPI, DR, ADDRESS, CITY, ZIP
FROM (
SELECT  A.NPI AS NPI ,
        A.DR_FULL_NAME AS DR ,
        A.ADDRESS_1 AS ADDRESS ,
        A.CITY AS CITY ,
        A.ZIP AS ZIP ,
        ROW_NUMBER() OVER ( PARTITION BY DR_FULL_NAME, ADDRESS_1, CITY, ZIP  ORDER BY NPI DESC) AS RN
FROM    OGEN.GEN_M_DOCTOR_MAST A
) S WHERE RN=1;
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000