SELECT Mysql - Prioritizing results that are not null

Asked

Viewed 281 times

4

Hello, I have a table with several records, which are not mandatory.

I need to execute a query, and bring only 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 return only him.

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

And least of all, it would be ID 3 that has only one value filled.

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

mysql select

  • As the answer of the colleagues, this rule answered me: SELECT *, SUM( (CASE WHEN Col-A IS NOT NULL THEN 1 ELSE 0 END) + (CASE WHEN Col-B IS NOT NULL THEN 1 ELSE 0 END) + (CASE WHEN Col-C IS NOT NULL THEN 1 ELSE 0 END) + (CASE WHEN Col-D IS NOT NULL THEN 1 ELSE 0 END) ) prioridade FROM tabela ORDER BY prioridade DESC LIMIT 1;

1 answer

2


You can do it on 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 the columns that are not null, sort decreasingly and limit the result in a row.

You may lose a bit of performance depending on how many records you have in the table and the indexes you set.

  • Cool idea @Roberto, I made it look like just accumulating a point for each value within SELECT and sort by this score. Pretty much the same logic you used.

Browser other questions tagged mysql select

You are not signed in. Login or sign up in order to post.