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
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
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
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.
