Get Distinct Record Based on Column. If latest val

Question

I am trying to select the Max value of each group based on the PrimaryMobile. The idea is that for each group I want to select its most recently entered value (based on its personID not on the DateCreated)

My data look likes

PersonID    PrimaryMobile    FirstName    LastName    City    DateCreated
1           34455666         CAD          Null        Pu      01-01-2014
2           34455666         ABC          AND         Null    02-01-2015
3           34455666         BFG          Null        Tu      Null
4           34567            New          ABC         Null    01-01-2014
5           34567            Null         Null        Ta      02-01-2014

Result that I want

PersonID    PrimaryMobile    FirstName    LastName    City    DateCreated
3           34455666         BFG          AND         Tu      02-01-2015
5           34567            New          ABC         Ta      02-01-2014

Please, let me know I can achieve this

Answer 1

This honestly feels like a mess but it is the best answer I can come up with. What I am doing below is breaking each column into a unique table. I am then performing the grouping function on the table, finding the most recent Non Null value for each column and rejoining the columns based on the PrimaryMobile. Also as a note since you did not give the table name I am going to assume the table name is "Person"

SQL

Select Max(Person.PersonID),
    Person.PrimaryMobile,
    FNameSet.FirstName,
    LNameSet.LastName,
    CitySet.City,
    DateSet.DateCreated
From Person
    Left Outer Join (Select FNamePerson.PersonID, 
                            FNamePerson.PrimaryMobile, 
                            FNamePerson.FirstName,
                            FNameRowID = ROW_NUMBER() OVER (PARTITION BY FNamePerson.PrimaryMobile ORDER BY FNamePerson.PersonID DESC)
                     From Person FNamePerson
                     Where FNamePerson.FirstName Is Not Null) FNameSet
        On Person.PrimaryMobile = FNameSet.PrimaryMobile
    Left Outer Join (Select LNamePerson.PersonID, 
                            LNamePerson.PrimaryMobile, 
                            LNamePerson.LastName,
                            LNameRowID = ROW_NUMBER() OVER (PARTITION BY LNamePerson.PrimaryMobile ORDER BY LNamePerson.PersonID DESC)
                     From Person LNamePerson
                     Where LNamePerson.LastName Is Not Null) LNameSet
        On Person.PrimaryMobile = LNameSet.PrimaryMobile
    Left Outer Join (Select CityPerson.PersonID, 
                            CityPerson.PrimaryMobile, 
                            CityPerson.City,
                            CityRowID = ROW_NUMBER() OVER (PARTITION BY CityPerson.PrimaryMobile ORDER BY CityPerson.PersonID DESC)
                     From Person CityPerson
                     Where CityPerson.City Is Not Null) CitySet
        On Person.PrimaryMobile = CitySet.PrimaryMobile
    Left Outer Join (Select DatePerson.PersonID, 
                            DatePerson.PrimaryMobile, 
                            DatePerson.DateCreated,
                            DateRowID = ROW_NUMBER() OVER (PARTITION BY DatePerson.PrimaryMobile ORDER BY DatePerson.PersonID DESC)
                     From Person DatePerson
                     Where DatePerson.DateCreated Is Not Null) DateSet
        On Person.PrimaryMobile = DateSet.PrimaryMobile
 Where FNameRowID = 1
   And LNameRowID = 1
   And CityRowID = 1
   And DateRowID = 1
Group By Person.PrimaryMobile,
         FNameSet.FirstName,
         LNameSet.LastName,
         CitySet.City,
         DateSet.DateCreated

Note though that this solution will remove a number if any one of the columns has no value assocuated with its group. ie if you removed NEW from the FirstName column of your dataset you wuold not get the second number in your result set.

Answer 2

It appears you're trying to get the "best" value out of each of your columns. "Best" being defined as not null and/or max value (even for personid - which doesn't appear to be your primary key). Also, it appears to you might want "ABC" instead of "ANB" for the Lastname value of 34567.

One way to do this is with subqueries for each of the select parameters. I've named your table t1:

SELECT DISTINCT t1_outer.PrimaryMobile,
  (SELECT MAX(PersonId) FROM t1 t1_inner WHERE t1_inner.PrimaryMobile = t1_outer.PrimaryMobile Where PersonId IS NOT NULL GROUP BY t1_inner.PrimaryMobile) AS PersonID,
  (SELECT MAX(FirstName) FROM t1 t1_inner WHERE t1_inner.PrimaryMobile = t1_outer.PrimaryMobile  WHERE FirstName IS NOT NULL GROUP BY t1_inner.PrimaryMobile) AS FirstName,
  (SELECT MAX(LastName) FROM t1 t1_inner WHERE t1_inner.PrimaryMobile = t1_outer.PrimaryMobile WHERE LastName IS NOT NULL GROUP BY t1_inner.PrimaryMobile) AS LastName,
(SELECT MAX(City) FROM t1 t1_inner WHERE t1_inner.PrimaryMobile = t1_outer.PrimaryMobile WHERE City IS NOT NULL GROUP BY t1_inner.PrimaryMobile) AS City,
(SELECT MAX(DateCreated) FROM t1 t1_inner WHERE t1_inner.PrimaryMobile = t1_outer.PrimaryMobile WHERE DateCreated IS NOT NULL GROUP BY t1_inner.PrimaryMobile) AS DateCreated
FROM t1 t1_outer

Answer 3

One way you can achieve your desired results is with a sub-select for every column (except PrimaryMobile):

SELECT 
(SELECT TOP 1 PersonId FROM MyTable t2 WHERE t2.PrimaryMobile=t1.PrimaryMobile ORDER BY PersonID DESC) AS PersonID,
t1.PrimaryMobile,
(SELECT TOP 1 FirstName FROM MyTable t2 WHERE t2.PrimaryMobile=t1.PrimaryMobile AND t2.FirstName IS NOT NULL ORDER BY PersonID DESC) AS FirstName
{Same as above replacing "FirstName" with "LastName" and "City"}
FROM MyTable t1
GROUP BY t1.PrimaryMobile