SQL Server Performance Forum – Threads Archive

Duplicates

I am using the following script but am getting duplicate records for SSN. How can I get rid of duplicates for the field NBR_SSN BEGIN DECLARE @nbr_energy_year BIGINT
SET @nbr_energy_year = 2004
SELECT DISTINCT RIGHT(‘000000000’ + CAST(app.NBR_SSN AS VARCHAR(9)),9) AS NBR_SSN,
UPPER (app.NAM_FIRST)AS NAM_FIRST,
UPPER (app.NAM_LAST) AS NAM_LAST,
app.DTE_DOB
FROM T_APPLN app WITH(NOLOCK)
INNER JOIN T_STATUS_APPLN tsa WITH (NOLOCK)
ON app.CDE_STATUS_APPLN = tsa.CDE_STATUS_APPLN
WHERE app.NBR_ENERGY_YEAR = @nbr_energy_year
AND app.CDE_STATUS_APPLN = 2
AND app.CDE_ACTIVE_FLAG = 1
GROUP BY app.NBR_SSN,
app.NAM_FIRST,
app.NAM_LAST,
app.DTE_DOB END Thanks!
"He laughs best who laughs last"

---

Post some sample data and the result you want Madhivanan Failing to plan is Planning to fail

---

NBR_SSN NAM_FIRST NAM_LAST DTE_DOB
————————————————————
185900003 ROSE MARRY 1960-04-12 267500120 BETTY HARRIS 1943-06-28 621192106 SUSHIEN YONG 1979-09-11 346670122 DAVIS JOHN 1965-07-10 087605420 ADAMS WINSTON 1958-06-18
Here SSN format shud be 9 digits with prefilled zero’s. Thanks!
"He laughs best who laughs last"

---

What is your expected result? Madhivanan Failing to plan is Planning to fail

---

When I execute that query some of the NBR_SSN fields are repeating, I dont want that to be repeated and that field shud be 9 digits with prefilled zero’s.
Thanks!
"He laughs best who laughs last"

---

From your query it seems you must be getting duplicate nbr_ssn column but not identicle rows.
that means data in other column is different for same nbr_ssn.
So in this case what value do you need for other columns i mean any top 1 value.

---

123111111 ADGSDGDSGDSDG JDJDJNNEEJJSKLS 1987-11-11 201205455 MARGARET GUMP 1952-01-01 087605420 ADAMS WINSTON NULL 204685650 SADE ROSS 1987-11-18 345345345 RD D 1945-02-12 191200612 DENNIS LILLEE 1976-06-03 087605420 ADAMS WINSTON NULL 123111111 JKS EREE 1976-10-03 The problem is I should get unique ssn#%92s irrespective of first name, last name, DOB. Even I get same SSN for different FNAME,LNAME,DOB .it is not considered. SSN#%92S SHOULD BE UNDUPLICATED. thanks. Thanks!
"He laughs best who laughs last"

---

In this case it would be better if you take nb_ssn column value grouped by so that you will get only distinct values and then based on that ssn value pick up the first,last,dob column value from the table which could be any.

---

try this: declare @t1 table(col1 varchar(9),col2 varchar(50),col3 varchar(50))
insert into @t1
SELECT ‘185900003’,’ROSE MARRY’,’1960-04-12′ union all
SELECT ‘185900003’,’BETTY HARRIS’,’1943-06-28′ union all
SELECT ‘621192106’,’SUSHIEN YONG’,’1979-09-11′ select * from @t1 select * from @t1 t1 where col2 in
(select top 1 col2 from @t1 where col1=t1.col1)

---

Will this suffice??? BEGIN DECLARE @nbr_energy_year BIGINT SET @nbr_energy_year = 2004 SELECT NBR_SSN, max(NAM_FIRST), max(NAM_LAST), max(app.DTE_DOB) FROM
(
SELECT RIGHT(‘000000000’ + CAST(app.NBR_SSN AS VARCHAR(9)),9) AS NBR_SSN,
UPPER (app.NAM_FIRST)AS NAM_FIRST,
UPPER (app.NAM_LAST) AS NAM_LAST,
app.DTE_DOB
FROM T_APPLN app WITH(NOLOCK)
INNER JOIN T_STATUS_APPLN tsa WITH (NOLOCK)
ON app.CDE_STATUS_APPLN = tsa.CDE_STATUS_APPLN
WHERE app.NBR_ENERGY_YEAR = @nbr_energy_year
AND app.CDE_STATUS_APPLN = 2
AND app.CDE_ACTIVE_FLAG = 1
)
GROUP BY NBR_SSN,
END
Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

---

Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

---

]]>