Is there a way to sort by a field only if it exists?

Dan_Rosen · 2025-02-24T15:50:42+00:00

There was an error rendering this rich post.

Dan_Rosen

Consider the following:

Source A

LEFT OUTER JOIN

Source B

ORDER BY

FieldFromA, FieldFromB

If I do this, it won't effectively do the left outer join as it needs the column from source B. Is there a way to only order by the second criteria if it's not null?

Find more posts tagged with

Accepted answers

All comments

Enrico Menotti

Hi Dan,

Just to be sure… have you confirmed that without the ORDER BY it is actually doing the LEFT OUTER JOIN?

Other than that, the only thing I can think of is to try IFNULL(FieldFromB, 1) in the ORDER BY. But I can't say whether that may be accepted.

Dan_Rosen

Hi Enrico,

Yes, in general with SQL if you include something in a clause it somewhat turns that outer join into an inner join.

I did try the IFNULL but it didn't accept that syntax.

In standard SQL, there are ways to work around this but I'm not sure what exists if anything for BQL.

I guess I was hoping there was some type of alternative to the <EVAL! !> we use in scripting that I could use to build some logic into the select, or some other functions that would help

Enrico Menotti

Try this:

SELECT…

FROM Source A

INNER JOIN

SELECT…

FROM Source B

ON…

ORDER BY FieldFromA, FieldFromB

UNION ALL (Space? I never remember…)

SELECT…

FROM Source A

LEFT OUTER JOIN

SELECT…

FROM Source B

WHERE FieldFromB IS NULL

ON…

ORDER BY FieldFromA

Dan_Rosen

That's an interesting idea, but I think when I union the two sources the order would get messed up. I need more of an overall sorting, so that source A where B is null comes right before source A where B isn't null.

I'm wondering if I might just have to do one script where I do the left outer joins and cast everything null as a dummy value. Then take that output and order it

Enrico Menotti

Yeah indeed my proposal isn't going to work, the sorting must be after the UNION if I'm right. Possibly you can do a variation:

SELECT…, FieldFromA

FROM Source A

INNER JOIN

SELECT…, FieldFromB

FROM Source B

ON…

UNION ALL (Space? I never remember…)

SELECT…, FieldFromA

FROM Source A

LEFT OUTER JOIN

SELECT…, 0

FROM Source B

WHERE FieldFromB IS NULL

ON…

ORDER BY FieldFromA, FieldFromB

Enrico Menotti

Or maybe:

SELECT…, FieldFromA

FROM Source A

INNER JOIN

SELECT…, FieldFromB

FROM Source B

ON…

UNION ALL (Space? I never remember…)

SELECT…, FieldFromA, NULL, …, NULL, 0

FROM Source A

LEFT OUTER JOIN

SELECT NULL

FROM Source B

WHERE FieldFromB IS NULL

ON…

ORDER BY FieldFromA, FieldFromB

Here in the SELECT after the UNION you put as many NULLs as the number of fields you have from Source B in the part before the UNION, minus 1, and in place of FieldFromB you put a zero.

(And probably you need a NULL after FieldFromB before the UNION to match the NULL in the second SELECT after the UNION.)

Sachin Choudhary

You can try the following:

Option 1: Replace the null values during extraction using a query-based source.

Option 2: Use the Null Value Replacement option in the source