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/