Re: Always getting back a row, even with no results

with x as
(select id,1 mark from my_table where cat = 3
union all
select null,0 mark)
select id from x where mark = (select max(mark) from x) alia;

Don't have SQL right now so can't test it.

On Fri, Aug 11, 2017 at 8:43 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Fri, Aug 11, 2017 at 8:36 AM, Jonathan Moules <
> jonathan-lists(at)lightpear(dot)com> wrote:
>
>> Hi David,
>> I'm afraid I don't really understand this response (I've not done much
>> with arrays), but it doesn't seem to work for my purpose.
>>
>> No NULL is returned if there is no result (i.e. cat = 50); instead,
>> there's simply no rows.
>>
>> What aspect of Arrays is this trying to take advantage of?
>>
>
> ​My bad, I had tested the "false" version with a single record, without
> the array, and it indeed works. But the scalar subselect prevents the
> inner query from returning more than one row. I added the array to handle
> the multiple rows setup (which required testing the true path) and forgot
> to go back and test the false path.
>
> The idea of the array was to keep the inner subquery scalar.
>
> The following works on 9.5 - not positive whether it will on 10 though, we
> made some changes in this area.
>
> SELECT
> unnest(
> COALESCE(
> (SELECT array_agg(col) FROM ( VALUES (1), (2) ) vals (col) WHERE true),
> ARRAY[null]::int[]
> )
> );
>
> David J.
>
>