Re: [SQL] selecting newer rows

  <p>Carlos Henrique Righetto Moreira ha scritto: <blockquote type="CITE">Hi. Thats my problem: <br />I have a table
withsome fields and a thousand of rows. By a mistake in the <br />past someone entered duplicated information (the key
fieldis different). <br />I need to select some rows from this table but I just want only the newer <br />row from the
tablein the case that row is duplicated. I made myself clear? <br />Anyone can help me? <p>-carlos
<p>************</blockquote><tt>Trythis:</tt><tt></tt><p><tt>---all the data in my table: (note that newer rows have
oidgreater)</tt><tt></tt><p><tt>prova=> select oid,* from prova order by nome;</tt><br /><tt>  
oid|chave|nome</tt><br/><tt>------+-----+--------</tt><br /><tt>500663|    2|Carlos</tt><br /><tt>500667|  
20|Carlos</tt><br/><tt>500670|   43|David</tt><br /><tt>500662|    1|Henrique</tt><br /><tt>500665|  
10|Henrique</tt><br/><tt>500664|    3|Jose</tt><br /><tt>500666|   30|Jose</tt><br /><tt>500669|   33|Manuel</tt><br
/><tt>500668|  31|Miriam</tt><br /><tt>(9 rows)</tt><tt></tt><p><tt>---only duplicate names:</tt><br /><tt>prova=>
selectnome from prova group by nome having count(*) > 1;</tt><br /><tt>nome</tt><br /><tt>--------</tt><br
/><tt>Carlos</tt><br/><tt>Henrique</tt><br /><tt>Jose</tt><br /><tt>(3 rows)</tt><tt></tt><p><tt>---and now show the
oidto see which row is newer:</tt><tt></tt><p><tt>prova=> select oid,* from prova where nome in (select nome from
provagroup by nome having  1 < count(*) );</tt><br /><tt>ERROR:  pull_var_clause: Cannot handle node type
108</tt><tt></tt><p><tt>oopsI think I found a bug :(</tt><tt></tt><p><tt>any way, you can do this by hand.</tt><br
/><tt>Forexample:</tt><br /><tt></tt> <tt></tt><p><tt>prova=> select oid,* from prova where nome = 'Carlos';</tt><br
/><tt>  oid|chave|nome</tt><br /><tt>------+-----+------</tt><br /><tt>500663|    2|Carlos</tt><br /><tt>500667|  
20|Carlos</tt><br/><tt>(2 rows)</tt><br /><tt></tt> <tt></tt><p><tt>The oid 500667 is newer than
500663</tt><tt></tt><p><tt>Ihope this help.</tt><tt></tt><p><tt>Boa sorte!</tt><br /><tt>José</tt><br /><tt></tt>  <br
/><tt></tt> <br /><tt></tt>