CODEWITHSUNDEEP
sqlpostgresql
shaunc
shaunc

Reputation: 5611

sort table with null as "insignificant"

I have a table with two columns: col_order (int), and name (text). I would like to retrieve ordered rows such that, when col_order is not null, it determines the order, but when its null, then name determines the order. I thought of an order by clause such as

order by coalesce( col_order, name )

However, this won't work because the two columns have different type. I am considering converting both into bytea, but: 1) to convert the integer is there a better method than just looping moding by 256, and stacking up individual bytes in a function, and 2) how do I convert "name" to insure some sort of sane collation order (assuming name has order ... well citext would be nice but I haven't bothered to rebuild to get that ... UTF8 for the moment).

Even if all this is possible (suggestions on details welcome) it seems like a lot of work. Is there a better way?

EDIT

I got an excellent answer by Gordon, but it shows I didn't phrase the question correctly. I want a sort order by name where col_order represents places where this order is overridden. This isn't a well posed problem, but here is one acceptable solution:

col_order| name
----------------
    null | a
       1 | foo
    null | foo1
       2 | bar

Ie -- here if col_order is null name should be inserted after name closest in alphabetical order but less that it. Otherwise, this could be gotten by:

order by col_order nulls last, name

EDIT 2

Ok ... to get your creative juices flowing, this seems to be going in the right direction:

with x as ( select *, 
    case when col_order is null then null else row_number() over (order by col_order) end as ord
  from temp )
select col_order, name, coalesce( ord, lag(ord,1) over (order by name) + .5) as ord from x;

It gets the order from the previous row, sorted by name, when there is no col_order. It isn't right in general... I guess I'd have to go back to the first row with non-null col_order ... it would seem that sql standard has "ignore nulls" for window functions which might do this, but isn't implemented in postgres. Any suggestions?

EDIT 3

The following would seem close -- but doesn't work. Perhaps window evaluation is a bit strange with recursive queries.

with recursive x(col_order, name, n) as ( 
  select col_order, name, case when col_order is null then null 
      else row_number() over (order by col_order) * t end from temp, tot
  union all
  select col_order, name, 
    case when row_number() over (order by name) = 1 then 0 
      else lag(n,1) over (order by name) + 1 end from x
  where x.n is null ),
tot as ( select count(*) as t from temp )
select * from x;

Upvotes: 0

Views: 49

Answers (2)

shaunc
shaunc

Reputation: 5611

Ok .. the following seems to work -- I'll leave the question "unanswered" though pending criticism or better suggestions:

Using the last_agg aggregate from here:

with
tot as ( select count(*) as t from temp ),
x as (
    select col_order, name,  
        case when col_order is null then null 
            else (row_number() over (order by col_order)) * t end as n,
        row_number() over (order by name) - 1 as i
    from temp, tot )
select x.col_order, x.name, coalesce(x.n,last_agg(y.n order by y.i)+x.i, 0 ) as n
from x
left join x as y on y.name < x.name
group by x.col_order, x.n, x.name, x.i
order by n;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Just use multiple clauses:

order by (case when col_order is not null then 1 else 2 end),
         col_order,
         name

When col_order is not null, then 1 is assigned for the first sort key. When it is null, then 2 is assigned. Hence, the not-nulls will be first.

Upvotes: 3

Related Questions