Hi,
before I'll go any further - this is only thought-experiment. I do not
plan to use such queries in real-life applications. I was just presented
with a question that I can't answer in any logical way.
There are two simple queries:
#v+
with rok2005 (miesiac,wynik) as (VALUES (1,1),(2,2) ,(4,4),(5,NULL),(6,6)) ,rok2004 (miesiac,wynik) as (VALUES
(1,3) ,(3,3),(4,5) ,(6,6))
SELECT
distinct on (miesiac) *
FROM ( SELECT miesiac, 2005 as rok, wynik FROM rok2005 union all SELECT miesiac, 2004 as rok, wynik FROM rok2004
) as polaczone
ORDER BY miesiac, wynik desc;
#v-
#v+
with rok2005 (miesiac,wynik) as (VALUES (1,1),(2,2) ,(4,4),(5,NULL),(6,6)) ,rok2004 (miesiac,wynik) as (VALUES
(1,3) ,(3,3),(4,5) ,(6,6))
SELECT
distinct on (miesiac) *
FROM ( SELECT miesiac, 2004 as rok, wynik FROM rok2004 union all SELECT miesiac, 2005 as rok, wynik FROM rok2005
) as polaczone
ORDER BY miesiac, wynik desc;
#v-
They differ only in order of queries in union all part.
The thing is that they return the same result. Why isn't one of them returning
"2005" for 6th "miesiac"?
I know I'm not sorting using "rok", which means I'm getting "undefined
functionality". Fine. But what exactly is happening that regardless of
order of rows in subquery, I get the same, always lower, rok in output?
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
