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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Retrieving a NULL when using the Max Aggregate

Author  Topic 

stevenandler
Starting Member

42 Posts

Posted - 2013-04-30 : 11:18:23
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

737 Posts

Posted - 2013-04-30 : 11:34:22
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

42 Posts

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-30 : 11:50:08
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
   

- Advertisement -