This is something you could test.

CREATE TABLE #S(FirstName VARCHAR(20), LastName VARCHAR(20), Address VARCHAR(20), City VARCHAR(20), State VARCHAR(2)

, SSN VARCHAR(20), SomeNumber VARCHAR(20))

INSERT INTO #S

SELECT 'John', 'Doe', '123 Street', 'Atlanta', 'GA', '123456789', '001' UNION ALL

SELECT 'John', 'Doe', '123 Street', 'Atlanta', 'GA','123456789', '004' UNION ALL

SELECT 'John', 'Doe', '123 Street', 'Atlanta', 'GA','123456789', '007'

;with cte

as (select row_number() over(partition by FirstName,LastName, Address order by SomeNumber DESC) as rn

,FirstName,LastName, Address,SomeNumber from #S)

SELECT * FROM cte where rn = 1

Results:

rnFirstNameLastName AddressSomeNumber

1John Doe 123 Street007