Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 5840F63457E for ; Thu, 6 Aug 2009 02:21:22 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 58452-02 for ; Thu, 6 Aug 2009 05:21:09 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.186]) by mail.postgresql.org (Postfix) with ESMTP id 7943B633C16 for ; Thu, 6 Aug 2009 02:21:11 -0300 (ADT) Received: from webserv.wug.de ([194.25.154.138]) by mrelayeu.kundenserver.de (node=mreu2) with ESMTP (Nemesis) id 0MKv5w-1MYvPY3NgQ-000b4e; Thu, 06 Aug 2009 07:21:09 +0200 Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1) id 1MYvPY-0006Pa-00 for pgsql-sql@postgresql.org; Thu, 06 Aug 2009 07:21:08 +0200 Date: Thu, 6 Aug 2009 07:21:08 +0200 From: "A. Kretschmer" To: pgsql-sql@postgresql.org Subject: Re: two records per row from query Message-ID: <20090806052108.GA22410@a-kretschmer.de> References: <200908051651.13235.jfabiani@yolo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200908051651.13235.jfabiani@yolo.com> X-OS: Debian/GNU Linux - weil ich es mir Wert bin! User-Agent: Mutt/1.5.9i X-Provags-ID: V01U2FsdGVkX18TmvO5/SmITM9AUfly0ris/AmQgc6YA4HqpU8 D4KbdivLZLmBvg+HHNbOtlLi2ut38NChU/0cpyYxW6Mgm2omF7 +SweOtP/N/3FIQqNydjSg== X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.197 tagged_above=-10 required=5 tests=AWL=1.402, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200908/22 X-Sequence-Number: 32931 In response to John : > mytable > pkid > class_date. > sessionid > > select * from mytable > 1 2009/01/01 2101 > 2 2009/01/02 2101 > > I would like an SQL that would produce > > newtable > pkid, > class_date1, > class_date2, > sessionid1, > sessionid2 > > Select * from newtable > > 1 2009/01/01 2009/01/02 2101 2101 I will try, but i'm not sure if i understand you correctly. Your table contains only 2 rows and both rows contains the same sessionid. Can i use that sessionid to find the rows that belongs together? Okay, my table: test=*# select * from mytable ; pkid | class_date | sessionid ------+------------+----------- 1 | 2009-01-01 | 2101 2 | 2009-01-02 | 2101 3 | 2009-02-01 | 2102 4 | 2009-02-02 | 2102 5 | 2009-03-01 | 2103 6 | 2009-03-02 | 2103 (6 rows) As you can see, there are 3 different sessionid's. test=*# select distinct on (sessionid1,sessionid2) pkid, classdate1, classdate2, sessionid1, sessionid2 from (select least(a.pkid, b.pkid) as pkid, least(a.class_date, b.class_date) as classdate1, greatest(a.class_date, b.class_date) as classdate2, a.sessionid as sessionid1, b.sessionid as sessionid2 from mytable a inner join mytable b on (a.sessionid=b.sessionid)) foo order by sessionid1, sessionid2,pkid; pkid | classdate1 | classdate2 | sessionid1 | sessionid2 ------+------------+------------+------------+------------ 1 | 2009-01-01 | 2009-01-01 | 2101 | 2101 3 | 2009-02-01 | 2009-02-01 | 2102 | 2102 5 | 2009-03-01 | 2009-03-01 | 2103 | 2103 (3 rows) Hope that helps... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net