SELECT
f.year,
f.id,
c.name,
(f.value / p.value) AS per_capita
FROM
fish_catch AS f
JOIN
pop_total AS p
USING
(year, id)
INNER JOIN
countries AS c ON f.id = c.id
ORDER BY
(year = 2005), value, name
Seems to never end.... Why is redesigning tables so difficult?! :-))
And furthermore, it is really difficult to grab and formulate the
problem that I have now. Let's see:
The above SQL leads to a correct SQL result; but the "(year = 2005)"
changes the "pattern" of the output completely. Before, without
sorting by a specific year, it would look like this:
year value name
1995 NULL Afghanistan
2000 NULL Afghanistan
2005 NULL Afghanistan
2000 2365 Albania
2005 2065 Albania
1995 1160 Albania
2000 113157 Algeria
2005 126259 Algeria
1995 105872 Algeria
2000 832 American Samoa
2005 3943 American Samoa
1995 152 American Samoa
With specifying "ORDER BY (y_2005), value, name" I have this:
year value name
1995 0 Ethiopia
2000 0 Ethiopia
2000 0.5 Bosnia and Herzegovina
1995 0.5 Bosnia and Herzegovina
2000 0.5 Christmas Island
1995 0.5 Christmas Island
....
2005 0 Bosnia and Herzegovina
2005 0 Ethiopia
2005 0.5 Christmas Island
2005 0.5 Cocos (Keeling) Islands
But what I would need is this:
1995 0.5 Bosnia and Herzegovina
2000 0.5 Bosnia and Herzegovina
2005 0 Bosnia and Herzegovina
1995 0 Ethiopia
2000 0 Ethiopia
2005 0 Ethiopia
1995 0.5 Christmas Island
2000 0.5 Christmas Island
2005 0.5 Christmas Island
Looks similar to the first result, but all content would be sorted by
the year 2005 without separating it from the other years.
Hmmm.... don't know if this is clear...
Most grateful for any feedback,
Stef
---------------------------(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
