CODEWITHSUNDEEP
sqloracle-databaseselect
jijo
jijo

Reputation: 815

Sort columns in sql

I have a table with number of columns. Most of the columns except primary key columns can have nulls.
I need a query which will query this table and fetch the results in such a way that, the records which have nulls for all columns except the primary columns should be shown first.
In the below example, EmpId and name are primary keys and as shown in the expected result, the records with no data for non-primary columns should be on top of the results.
I have tried sorting based on all the non-primary columns but didn't get expected result.

enter image description here

Upvotes: 0

Views: 46

Answers (2)

Marc
Marc

Reputation: 11613

Use nulls first and order by all retrieved columns.

Select empid,
       name,
       address,
       phone,
       zip,
       gender
  from TABLE
 Order by empid nulls first,
          name nulls first,
          address nulls first, 
          phone nulls first,
          zip nulls first,
          gender nulls first;

And you really should supply the query(ies) you've tried.

Upvotes: 2

jarlh
jarlh

Reputation: 44716

If all of address, phone, zip, gender is NULL, then put row first. Else sort by pk etc.

Select empid,
       name,
       address,
       phone,
       zip,
       gender
  from TABLE
 Order by case when coalesce(address, phone, zip, gender) is null then 0 else 1 end,
          empid, name, address, phone, zip, gender;

Upvotes: 0

Related Questions