SELECT Mysql - Prioritizing result that is not null

Navigation
4

Hello, I have a table with multiple records, which are not required.

I need to run a query, and only bring the result that has more information that is not null.

Ex: 

ID | Col-A | Col-B | Col-C | Col-D
----------------------------------
1    null    null   true    true
2    true    null   true    true
3    null    null   null    true

In this example, ID 2 has three values filled in, I need my SELECT to return only it.

If it did not exist, it should return the record ID 1, which has 2 values filled in.

And the least important, would be ID 3 that has only one value filled in.

How to do this in Mysql, returning only 1 record, and that it is the most important (which has more values filled in)?

    
asked by anonymous 21.05.2018 / 01:32

1 answer

2

You can do this in ORDER BY : 

SELECT ID, Col-A, Col-B, Col-C, Col-D FROM tabela
ORDER BY (IF(Col-A IS NULL, 0, 1) + IF(Col-B IS NULL, 0, 1) + IF(Col-C IS NULL, 0, 1) + IF(Col-D IS NULL, 0, 1)) DESC
LIMIT 1;

The idea is to add non-null columns, sort descendingly, and limit the result to one line.

  

You may lose some performance depending on how many records you have in the table and the configured indexes.

    
21.05.2018 / 15:57
Hexagonal Matrix in Python Invalid property 'category.subcategory' of bean class. Error saving user to bank