DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:from;
	b=s/iXjiz51ZEhRWJZV3BsJ/F16VAMXqrCZkAcKZf1z29yp63axE0MHP/5WKwBHkcJohk2lWZS378ooqeNCnLJr8FgXGWoyVpaNOHJTN3t3AwzewibeLC/EphKrvADEd/K1S1EeWBbt/m8Xbm6eAsKNcDZC3XzkbvPcVCuIDoaVY4=
Message-ID: <4806168D.6080802@gmail.com>
Date: Wed, 16 Apr 2008 12:09:01 -0300
User-Agent: Thunderbird 1.5.0.12 (X11/20070530)
MIME-Version: 1.0
To: Bryan Emrys <bryan.emrys@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Data Comparison Single Table Question
References: <200804160634.27304.bryan.emrys@gmail.com>
In-Reply-To: <200804160634.27304.bryan.emrys@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
From: Osvaldo Rosario Kussama <osvaldo.kussama@gmail.com>

Bryan Emrys escreveu:
> I can handle this outside sql, but it seems like I should be able to do this in sql as well. 
> 
> 1 table: countries. 
> 3 columns: id, name, price
> 
> What I'm trying to get is a result of the price differences between every country.
> 
> So if the data looks like (ignoring the id field)
> 
> Taiwain 30
> UK 50
> US 40
> 
> I'm trying to build a matrix that looks like:
> 		Taiwan	UK	US
> Taiwan	0		-20	-10
> UK		20		0	10
> US		10		-10	0
> 


Not in a matrix form:

bdteste=# SELECT f1.name,f1.price,f2.name,f1.price-f2.price AS 
difference FROM foo f1 CROSS JOIN foo f2;
   name   | price |  name   | difference
---------+-------+---------+------------
  Taiwain |    30 | Taiwain |          0
  Taiwain |    30 | UK      |        -20
  Taiwain |    30 | US      |        -10
  UK      |    50 | Taiwain |         20
  UK      |    50 | UK      |          0
  UK      |    50 | US      |         10
  US      |    40 | Taiwain |         10
  US      |    40 | UK      |        -10
  US      |    40 | US      |          0
(9 registros)

Osvaldo