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/
