Script to get distinct data

Question

Post reply

Script to get distinct data

chinn

SSCrazy

Points: 2840

February 21, 2007 at 8:39 am

#116284

Hello,

I have like 75 rows in a table and we have some duplicates like this out of 75 is it possible to get just one row

from the below data.

ENO	Fname	Lname	Dept	Start	End	Chngdate
1	Sam	Jonson	Dept1	3/14/2005	3/31/2007	9/26/2006
1	Sam	Jonson	Dept2	3/14/2005	3/31/2007	9/26/2006
1	Sam	Jonson	Dept3	3/14/2005	3/31/2007	9/26/2006
1	Sam	Jonson	Dept4	3/14/2005	3/31/2007	9/26/2006

Any help on this is greatly appreciated.

Thanks,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

there's several ways...I would suggest a group by:

SELECT ENO, Fname, Lname, MIN(Dept) as Dept, MIN(Start) as Start, MIN(End) as [End], MIN(Chngdate) as Chngdate FROM sometable

group by ENO, Fname, Lname

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

chinn SSCrazy Points: 2840 More actions · Answer 2

Lowell,

Thanks for your Speedy response!

There are other columns also in that table also you want me to just add the columns in the select list and the group by list right.

Thanks,

Lowell SSC Guru Points: 323518 More actions · Answer 3

i kind of assumed that the uniqueness you wanted was firstname/lastname, so all other columns would be a min() or max(), unless those columns make the row the distinct -ness you are looking for; if they are, you'd add them to the group by section.

HTH.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

Using a simple GROUP BY and MIN may give result records where each aggregate field returns a value from a different source record. This may not be acceptable.

If the records have a single-field primary key (INT IDENTITY for instance), you can get unique records with:

SELECT * FROM table t

INNER JOIN (

SELECT MIN(pk) AS pk FROM table

GROUP BY f1, f2, ...

) k ON t.pk = k.pk

I have had cases where there was a complex set of factors that determined the "best" record from each set of duplicates. This can be handled using a temp table and a unique index:

CREATE TABLE #t (

...)

CREATE UNIQUE INDEX ix ON #t (f1, f2, ...) WITH IGNORE_DUP_KEY

INSERT INTO #t

SELECT ... FROM table

ORDER BY CASE WHEN condition1 THEN weight1 ELSE 0 END

+ CASE WHEN condition2 THEN weight2 ELSE 0 END

+ CASE WHEN condition3 THEN weight3 ELSE 0 END