From righetto@isnet.com.br Thu Aug 19 14:29:24 1999 Received: from yoda.isnet.com.br (IDENT:root@[200.215.24.162]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA39391 for ; Thu, 19 Aug 1999 10:29:24 -0400 (EDT) (envelope-from righetto@isnet.com.br) Received: from han ([192.168.36.2]) by yoda.isnet.com.br (8.9.3/8.8.5) with SMTP id MAA06917 for ; Thu, 19 Aug 1999 12:48:56 -0300 Message-ID: <00a701beea4f$6b443260$0224a8c0@isnet.com.br> From: "Carlos Henrique Righetto Moreira" To: Subject: selecting newer rows Date: Thu, 19 Aug 1999 11:30:41 -0300 MIME-Version: 1.0 Content-Type: text/plain; charset="x-user-defined" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 Hi. Thats my problem: I have a table with some fields and a thousand of rows. By a mistake in the past someone entered duplicated information (the key field is different). I need to select some rows from this table but I just want only the newer row from the table in the case that row is duplicated. I made myself clear? Anyone can help me? -carlos From jose@sferacarta.com Mon Aug 23 13:29:35 1999 Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.9.3/8.9.3) with ESMTP id JAA19917 for ; Mon, 23 Aug 1999 09:29:35 -0400 (EDT) (envelope-from jose@sferacarta.com) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.9.3/8.9.3/NETTuno 3.3) with ESMTP id PAA26986; Mon, 23 Aug 1999 15:29:12 +0200 (MDT) Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5] by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian)) id 11Ivjy-0008LG-00; Mon, 23 Aug 1999 15:10:30 +0000 Message-ID: <37C1495E.E9AF4D8F@sferacarta.com> Date: Mon, 23 Aug 1999 15:15:10 +0200 From: =?iso-8859-1?Q?Jos=E9?= Soares X-Mailer: Mozilla 4.5 [it] (Win95; I) X-Accept-Language: it MIME-Version: 1.0 To: Carlos Henrique Righetto Moreira , sql Subject: Re: [SQL] selecting newer rows References: <00a701beea4f$6b443260$0224a8c0@isnet.com.br> Content-Type: multipart/alternative; boundary="------------150DD37F1265BEE7983DD720" --------------150DD37F1265BEE7983DD720 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Carlos Henrique Righetto Moreira ha scritto: > Hi. Thats my problem: > I have a table with some fields and a thousand of rows. By a mistake in the > past someone entered duplicated information (the key field is different). > I need to select some rows from this table but I just want only the newer > row from the table in the case that row is duplicated. I made myself clear? > Anyone can help me? > > -carlos > > ************ Try this: ---all the data in my table: (note that newer rows have oid greater) prova=> select oid,* from prova order by nome; oid|chave|nome ------+-----+-------- 500663| 2|Carlos 500667| 20|Carlos 500670| 43|David 500662| 1|Henrique 500665| 10|Henrique 500664| 3|Jose 500666| 30|Jose 500669| 33|Manuel 500668| 31|Miriam (9 rows) ---only duplicate names: prova=> select nome from prova group by nome having count(*) > 1; nome -------- Carlos Henrique Jose (3 rows) ---and now show the oid to see which row is newer: prova=> select oid,* from prova where nome in (select nome from prova group by nome having 1 < count(*) ); ERROR: pull_var_clause: Cannot handle node type 108 oops I think I found a bug :( any way, you can do this by hand. For example: prova=> select oid,* from prova where nome = 'Carlos'; oid|chave|nome ------+-----+------ 500663| 2|Carlos 500667| 20|Carlos (2 rows) The oid 500667 is newer than 500663 I hope this help. Boa sorte! José --------------150DD37F1265BEE7983DD720 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit  

Carlos Henrique Righetto Moreira ha scritto:

Hi. Thats my problem:
I have a table with some fields and a thousand of rows. By a mistake in the
past someone entered duplicated information (the key field is different).
I need to select some rows from this table but I just want only the newer
row from the table in the case that row is duplicated. I made myself clear?
Anyone can help me?

-carlos

************

Try this:

---all the data in my table: (note that newer rows have oid greater)

prova=> select oid,* from prova order by nome;
   oid|chave|nome
------+-----+--------
500663|    2|Carlos
500667|   20|Carlos
500670|   43|David
500662|    1|Henrique
500665|   10|Henrique
500664|    3|Jose
500666|   30|Jose
500669|   33|Manuel
500668|   31|Miriam
(9 rows)

---only duplicate names:
prova=> select nome from prova group by nome having count(*) > 1;
nome
--------
Carlos
Henrique
Jose
(3 rows)

---and now show the oid to see which row is newer:

prova=> select oid,* from prova where nome in (select nome from prova group by nome having  1 < count(*) );
ERROR:  pull_var_clause: Cannot handle node type 108

oops I think I found a bug :(

any way, you can do this by hand.
For example:
 

prova=> select oid,* from prova where nome = 'Carlos';
   oid|chave|nome
------+-----+------
500663|    2|Carlos
500667|   20|Carlos
(2 rows)
 

The oid 500667 is newer than 500663

I hope this help.

Boa sorte!
José
 
 
  --------------150DD37F1265BEE7983DD720--