CODEWITHSUNDEEP
sqloracle-databaseoracle10gsql-order-by
BenoitParis
BenoitParis

Reputation: 3184

Ordering by Nullness of field

Hello all :) I'm strugglind to come up with the right SQL syntax in Oracle 10g. I would like to come up with something like this:

SELECT
  LAST_VALUE FIELD_INFO OVER(ORDER BY FIELD_1 IS NULL, FIELD_2, FIELD_1)
FROM TABLE_1

FIELD_1 IS NULL raising a syntax error.

How would you do it?

Upvotes: 0

Views: 70

Answers (2)

David Aldridge
David Aldridge

Reputation: 52346

NULLs First

This expression is a compact Oracle syntax to return 0 for Nulls and 1 for non-Nulls

Order by NVL2(FIELD_1,1,0), ...

Or you could use a case statement:

Order by Case when FIELD_1 is null then 0 else 1 end, ...

NULLs Last

Order by NVL2(FIELD_1,0,1)

Order by Case when FIELD_1 is null then 1 else 0 end, ...

There's possibly a fractional optimisation in this method:

Order by Case when FIELD_1 is null then null else 0 end nulls last, ...

... through requiring slightly less sort area.

Upvotes: 4

David Jashi
David Jashi

Reputation: 4511

I think you should make a compound field and sort by it

COALESCE(FIELD_1,'[lots of spaces to ensure they go first]')||FIELD_2

But it would really help if you post 10-20 example records to show exactly, what you want to achieve.

Upvotes: 0

Related Questions