CODEWITHSUNDEEP
mysqlconditional-statements
Mokkun
Mokkun

Reputation: 726

Select with condition

I need some help about select with condition, right now I'm doing this:

                GROUP_CONCAT(
                    CASE WHEN glpi_tickets.users_id_lastupdater = glpi_users.id THEN
                        CONCAT(glpi_users.firstname, ' ', glpi_users.realname) 
                    END SEPARATOR '<br>') AS last_updater

I select firstname and realname when users_id_lastupdater = id

I guess there is a better way to do this?

Upvotes: 0

Views: 80

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270593

group_concat seems like overkill for this. You could just use max():

max(CASE WHEN glpi_tickets.users_id_lastupdater = glpi_users.id
         THEN CONCAT(glpi_users.firstname, ' ', glpi_users.realname) 
    END) AS last_updater

The separator isn't needed because it isn't used for only one element.

EDIT:

The max() function takes the maximum of value of an argument. In this case, it is conditional because of the case. When the condition is not met, the values are NULL (no else clause). So, it retrieves the value when the condition is met. If multiple row match the condition, it retrieves the largest value.

Upvotes: 1

Related Questions