Need to exclude the data which have multiple records

I am trying to build a query to get the the unique data from data base, but here the problem I’m facing there is some records which have multiple records and those records have null and not null values.. I have to pick the value of the record which have the latest created date and it’s data is null.. But I’m getting the data for which latest record having not null values..

For ex: I have record which have 3 records with date in feb month, sept, and oct month.. I have created a query to get the latest record based on max condition but when I’m using where condition to get the null value records.. It is taking the record of feb.. How can I stop getting the value of record which is not latest one.

Sample query:

Select * from ( select col1,date, col3, max(date),count(col1) from table group by col1,date,col3 having count(col1) <=1) where col3 is null

Here I’m expecting that it should filter the records which have the latest records but that is not happening because here one record is having null and one record is not null.. So how to overcome this

Answer

Your query may not run as-is. You need to give your subquery a name and also in a sub-select, you need to give your derived fields a name e.g count(a) needs a name e.g. count_a. See my amended SQL below. I have tested this code and it runs. I only added the x as a name for my sub-query and I named the sum fields as Party_Address_Start_Dt and as Count_Address_Id

SELECT *
FROM   (SELECT Address_Id,
               Party_Role_Cd,
               Address_Close_Reason_Cd,
               MAX(Party_Address_Start_Dt) AS Party_Address_Start_Dt,
               COUNT(Address_Id)           AS Count_Address_Id
        FROM   party_address_hist
        GROUP  BY Address_Id,
                  Party_Role_Cd,
                  Address_Close_Reason_Cd
        HAVING COUNT(Address_Id) <= 1) x
WHERE  Address_Close_Reason_Cd IS NULL