Need to remove duplicate records and get distinct values

Question

Post reply

Need to remove duplicate records and get distinct values

Sanchita

Old Hand

Points: 369
More actions
July 15, 2008 at 4:44 am

#191001

Hi
I am sorry if someone has already posted this question and am asking again.
This problem is eating my head, I have a table
Table 1 -
1br
1er
2rt
3yh
2uj
3iu
3ol
Now I want only 1 row for each unique id, that row could be any row but id shouldn't repeat.
1br
2rt
3yh
or
1er
2uj
3ol
Thanks

Viewing 15 posts - 1 through 15 (of 58 total)

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

karthik M One Orange Chip Points: 29139 More actions · Answer 1

karthik M

One Orange Chip

Points: 29139

July 15, 2008 at 5:37 am

#842971

use distinct keyword

karthik

d_sysuk SSC Eights! Points: 809 More actions · Answer 2

Try something like this:

select distinct id, min(value) value

from

(select '1' id,'br' value

union

select '1','er'

union

select '2','rt'

union

select '2','uj'

union

select '3','iu'

union

select '3','ol') data

group by id

-- Output:

idvalue

1br

2rt

3iu

Sanchita Old Hand Points: 369 More actions · Answer 3

Thanks min max functions are an easy way to do it

Dugi SSCoach Points: 17998 More actions · Answer 4

Ooppsss I didn't see in which forum we are discussing ... sorry!

I posted the technique for the SQL Server 2005!

there is one of the solutions:

you should add the column as identity column incremented by one and performing the delete operation like this:

--ADDING THE IDENTITY COLUMN

ALTER TABLE table1

ADD IDENT INT IDENTITY(1,1);

GO

after that you will write this code:

DELETE FROM table1 WHERE IDENT NOT IN (SELECT MIN(IDENT) FROM TABLE1 GROUP BY ID);

SELECT * FROM TABLE1 --YOU WILL FIND THE DISTINCT VALUES OF YOUR TABLE

these techniques posted many time in many locations, also in SSC!

:w00t:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

Even in SQL Server 2000, which is the this database forum?

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

karthikeyan (7/15/2008)
use distinct keyword

Let's see how you would do that, Karthik... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

d_sysuk SSC Eights! Points: 809 More actions · Answer 7

You can actually get rid of the distinct part of the example query, as this is implicit by using the group by and serves no purpose:

select id, max(value) value

from

(select '1' id,'br' value

union

select '1','er'

union

select '2','rt'

union

select '2','uj'

union

select '3','iu'

union

select '3','ol'

union

select '3','ol') data

group by id

Output:

idvalue

1er

2uj

3ol

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 8

Heh... you guys are missing the real problem! 😉 The real problem is why does the OP NOT care about which data is returned? If you don't care, then it shouldn't be returned to begin with because it doesn't mean anything real. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 9

Maybe it's just a starting condition?

"Get these records and update a flag".

Call a SP to process the previously marked records.

Now start from beginning by picking a random record for each group.

"Get these records and update a flag".

Call a SP to process the previously marked records.

Now start from beginning by picking a random record for each group.

...

Until there are no more records?

N 56°04'39.16"
E 12°55'05.25"

Sanchita Old Hand Points: 369 More actions · Answer 10

Jeff Moden (7/15/2008)
Heh... you guys are missing the real problem! 😉 The real problem is why does the OP NOT care about which data is returned? If you don't care, then it shouldn't be returned to begin with because it doesn't mean anything real. 😉

hehe! 🙂

that brings me to my next question - how would i get the 2nd / 3rd /4th ..... element for any particular id..

i was wondering if i cud have another column which wud tell whether its the 0th, 1st, 2nd.... element for that id ?

1 br 0

1 er 1

2 rt 0

3 yh 0

2 uj 1

3 iu 1

3 ol 2

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 11

There are a couple of ways... I believe the fastest for large data sets in SQL Server 2000 would be in the following...

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 12

Select distinct id, Name = ''

into #t1

from Data

update #t1

set Name = Name

from Data

where #t1.id = Data.id

karthik

Dugi SSCoach Points: 17998 More actions · Answer 13

Hi Jeff!

Your code here just selecting and ordering asc the data inside the table it doesn't do any elimination of duplicate records!

Can you explain little bit this!

Dugi

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Telammica Ten Centuries Points: 1288 More actions · Answer 14

well - try this

create table test99

(seq int, strin varchar(2))

insert into test99

values (3, 'ol')

insert into test99

values (3, 'iu')

select distinct z.seq,

(select top 1 strin

from test99 x

where x.seq = z.seq)

from test99 z