Question 232229 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Sort database table to bring rows having data for all columns first

In MySQL5.7, I have a column having 28 columns and 4000 rows. I want to bring rows having data for all or rows having data for maximum columns first and then for the other rows to progressively follow. How do I achieve this ?

Eg:
Table : Student

name age address
tan 10
gib 10 california
hal
pur 12

Expected Output: ‘gib’ should come first, followed by either ‘tan’ or ‘pur’ since they both have data for only 1 column, followed by ‘hal’ which doesn’t have any data.

Tags:

2

Answers


  1. 0

    Assuming the name column would never have null values, we can try:

    SELECT name, age, address
FROM yourTable
ORDER BY
    name,
    age IS NULL,      -- non null age first
    age,              -- sort ascending by age
    address IS NULL,  -- non null address first
    address;          -- sort ascending by address
    Login or Signup to reply.
  2. 0

    Use this ORDER BY clause:

    ORDER BY (column1 IS NOT NULL) + 
         (column2 IS NOT NULL) + 
         (column3 IS NOT NULL) +
         ....................... 
         (column28 IS NOT NULL) DESC;

    Each of the columnX IS NOT NULL expressions evaluates to 1 for TRUE or 0 for FALSE.
    What you get by summing them is the number of non–NULL columns in each row and you can use this numeric result to sort the rows descending.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search