am Fri, dem 05.10.2007, um 9:13:10 +0200 mailte Stefan Schwarzer folgendes: > >> > >>And the next question coming up is: How should my query look like so > >>that I can sort the (HTML) table by a specific year in ascending or > >>descending order? So, that it doesn't display it by the country names > >>alphabetical order, but by, say 1998? > > > >If you have only one row, how would you sort this result? ;-) > > With the "old" design I had up to 240 rows - for each country/ > territory one row. Now, I have 240*num_years rows. Maybe i don't understand you. I extend the table: test=# select * from vals ; year | val | country ------+--------+--------- 1998 | 10.00 | a 1999 | 30.00 | a 2000 | 100.00 | a 1998 | 20.00 | b 1999 | 39.00 | b 2000 | 99.00 | b 1998 | 25.00 | c 2000 | 98.00 | c (8 rows) Now, select for every country and every year all in one row (in the example only for 1998 and country a and b, i'm to lazy): test=*# select sum(case when year=1998 and country='a' then val else 0 end) as "1998_a", sum(case when year=1998 and country='b' then val else 0 end) as "1998_b", sum(case when year=1999 then val else 0 end) as "1999", sum(case when year=2000 then val else 0 end) as "2000" from vals; 1998_a | 1998_b | 1999 | 2000 --------+--------+-------+-------- 10.00 | 20.00 | 69.00 | 297.00 (1 row) you can see: sort by year and country, and all in one line ;-) Or, one row for every country, ordered by the country's name: test=*# select country, sum(case when year=1998 then val else 0 end) as "1998", sum(case when year=1999 then val else 0 end) as "1999", sum(case when year=2000 then val else 0 end) as "2000" from vals group by country order by country; country | 1998 | 1999 | 2000 ---------+-------+-------+-------- a | 10.00 | 30.00 | 100.00 b | 20.00 | 39.00 | 99.00 c | 25.00 | 0 | 98.00 (3 rows) Btw.: i'm subscribed on the list, please no extra CC to me. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
