pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
merge two rows where value are null 4+ messages / 3 participants [nested] [flat]
* merge two rows where value are null @ 2007-08-14 10:52 giuseppe.derossi@email.it 2007-08-14 13:34 ` Re: merge two rows where value are null Ben Kim <bkim@tamu.edu> 0 siblings, 1 reply; 4+ messages in thread From: giuseppe.derossi@email.it @ 2007-08-14 10:52 UTC (permalink / raw) To: pgsql-admin Hi all, sorry if OT. I have the following using case: Name, Surname , Job, Hobby, Pet, address John, Smith, NULL, photo, NULL, NULL John, Smith, student, NULL, cat, NULL by using name and surname as selecting key, I want : John, Smith, student, photo, cat, NULL that is I want to substitute the null valus of the LAST row with not-null value of previous one. how can I perform that ? in may installed vesion there aren't function as merge and collect. Thanks in advance GIU -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Entra in Mondolastminute, centinaia di offerte ti aspettano per le tue vacanze a prezzi lastminute! Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6851&d=20070814 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: merge two rows where value are null 2007-08-14 10:52 merge two rows where value are null giuseppe.derossi@email.it @ 2007-08-14 13:34 ` Ben Kim <bkim@tamu.edu> 0 siblings, 0 replies; 4+ messages in thread From: Ben Kim @ 2007-08-14 13:34 UTC (permalink / raw) To: giuseppe.derossi@email.it; +Cc: pgsql-admin On Tue, 14 Aug 2007, giuseppe.derossi@email.it wrote: > Name, Surname , Job, Hobby, Pet, address > John, Smith, NULL, photo, NULL, NULL > John, Smith, student, NULL, cat, NULL > by using name and surname as selecting key, I want : > > John, Smith, student, photo, cat, NULL If you are sure there's no conflict (multiple values) for a (name, surname) key, one simple solution might be (surely not an efficient one) select (select distinct Name from thetable where Name = 'name1' and Surname = 'surname1' where Name is not null), (select distinct Surname from thetable where Name = 'name1' and Surname = 'surname1' where Surname is not null), (select distinct Job from thetable where Name = 'name1' and Surname = 'surname1' where Job is not null), (select distinct Hobby from thetable where Name = 'name1' and Surname = 'surname1' where Hobby is not null), (select distinct Pet from thetable where Name = 'name1' and Surname = 'surname1' where Pet is not null), (select distinct address from thetable where Name = 'name1' and Surname = 'surname1' where address is not null) This will fail if you also have John, Smith, student, NULL, dog, NULL in addition to > John, Smith, student, NULL, cat, NULL You can test for offending rows by: select array_to_string( array( select distinct Pet from thetable where Name = 'name1' and Surname = 'surname1' where Pet is not null),',') and so on... Regards, Ben K. Developer http://benix.tamu.edu ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: merge two rows where value are null @ 2007-08-14 13:48 giuseppe.derossi@email.it 2007-08-14 14:23 ` Re: merge two rows where value are null Kevin Grittner <Kevin.Grittner@wicourts.gov> 0 siblings, 1 reply; 4+ messages in thread From: giuseppe.derossi@email.it @ 2007-08-14 13:48 UTC (permalink / raw) To: Ben Kim <bkim@tamu.edu>; +Cc: pgsql-admin Thanks for the suggestion, I was studying a solution which foresees the 'case when' construct... now I can use it in addition to array_to_string . In fact my solution failed too if <it's raining cats and dogs :-) >, but if there are some more values I get them all as well. thanks --------- Original Message -------- Da: Ben Kim <bkim@tamu.edu> To: Cc: pgsql-admin@postgresql.org Oggetto: Re: [ADMIN] merge two rows where value are null Data: 14/08/07 15:38 > > > > On Tue, 14 Aug 2007, giuseppe.derossi@email.it wrote: > > > Name, Surname , Job, Hobby, Pet, address > > John, Smith, NULL, photo, NULL, NULL > > John, Smith, student, NULL, cat, NULL > > > by using name and surname as selecting key, I want : > > > > John, Smith, student, photo, cat, NULL > > If you are sure there's no conflict (multiple values) for a (name, > surname) key, one simple solution might be (surely not an efficient one) > > select > (select distinct Name from thetable where Name = 'name1' and Surname = 'surname1' where Name is not null), > (select distinct Surname from thetable where Name = 'name1' and Surname = 'surname1' where Surname is not null), > (select distinct Job from thetable where Name = 'name1' and Surname = 'surname1' where Job is not null), > (select distinct Hobby from thetable where Name = 'name1' and Surname = 'surname1' where Hobby is not null), > (select distinct Pet from thetable where Name = 'name1' and Surname = 'surname1' where Pet is not null), > (select distinct address from thetable where Name = 'name1' and Surname = 'surname1' where address is not null) > > This will fail if you also have > John, Smith, student, NULL, dog, NULL > > in addition to > > John, Smith, student, NULL, cat, NULL > > You can test for offending rows by: > > select > array_to_string( array( select distinct Pet from thetable where Name = 'name1' and Surname = 'surname1' where Pet is not null),',') > > and so on... > > > Regards, > > Ben K. > Developer > http://benix.tamu.edu > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > > > -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Hai bisogno di contanti per realizzare i tuoi desideri? Prometeo ti propone prestiti da 1.500 a 31.000 Euro! Clicca qui per un preventivo immediato. Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6916&d=20070814 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: merge two rows where value are null 2007-08-14 13:48 Re: merge two rows where value are null giuseppe.derossi@email.it @ 2007-08-14 14:23 ` Kevin Grittner <Kevin.Grittner@wicourts.gov> 0 siblings, 0 replies; 4+ messages in thread From: Kevin Grittner @ 2007-08-14 14:23 UTC (permalink / raw) To: giuseppe.derossi@email.it; Ben Kim <bkim@tamu.edu>; +Cc: pgsql-admin >>> On Tue, Aug 14, 2007 at 8:48 AM, in message <b1a365afcf3059983eecc3db74a3cadf@83.225.75.94>, <giuseppe.derossi@email.it> wrote: > I was studying a solution which foresees the 'case when' construct... > now I can use it in addition to array_to_string . In fact my solution > failed too if <it's raining cats and dogs :-) >, but if there are some more > values I get them all as well. Is there a timestamp, date, or sequence number that you can use to break ties? Without something like that, I don't think you can do what you want with set-based logic -- you would need to do something procedural without any guarantee that you'd get the same results in two different runs. -Kevin ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-08-14 14:23 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2007-08-14 10:52 merge two rows where value are null giuseppe.derossi@email.it 2007-08-14 13:34 ` Ben Kim <bkim@tamu.edu> 2007-08-14 13:48 Re: merge two rows where value are null giuseppe.derossi@email.it 2007-08-14 14:23 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox