Re: How to remove duplicate lines but save one of the lines?

A B wrote:
> I have a table with rows like this
> A 1
> A 1
> B 3
> B 3
> C 44
> C 44
> and so on.
>
> and I want it to be
> A 1
> B 3
> C 44
>
> so how can I remove the all the duplicate lines but one?
>
>
Try with:

your table structure for example: create table yourtable(campo1 char,
num integer);

select * from yourtable;

sicodelico=# select * from yourtable ;
campo1 | num
--------+-----
A | 1
A | 1
B | 3
B | 3
C | 44
C | 44
(6 filas)

sicodelico=#

1) create temp sequence foo_id_seq start with 1;

2) alter table yourtable add column id integer;

3) update yourtable set id = nextval('foo_id_seq');

look this:

sicodelico=# select * from yourtable ;
campo1 | num | id
--------+-----+----
A | 1 | 1
A | 1 | 2
B | 3 | 3
B | 3 | 4
C | 44 | 5
C | 44 | 6
(6 filas)

4) delete from yourtable where campo1 in (select y.campo1 from yourtable
y where yourtable.id > y.id);

sicodelico=# select * from yourtable;
campo1 | num | id
--------+-----+----
A | 1 | 1
B | 3 | 3
C | 44 | 5
(3 filas)

5) alter table yourtable drop column id;

sicodelico=# select * from yourtable;
campo1 | num
--------+-----
A | 1
B | 3
C | 44
(3 filas)

have a lot of fun :)

--
Regards,

Julio Cesar Sánchez González.

--
Ahora me he convertido en la muerte, destructora de mundos.
Soy la Muerte que se lleva todo, la fuente de las cosas que vendran.

www.sistemasyconectividad.com.mx http://darkavngr.blogspot.com/