Untitled

SELECT message.uid as id, message.text as text, message.createDate as createDate,
        message.type as type,
        profile.firstName as firstName, profile.lastName as lastName, profile.avatar as avatar,
        message.read as read, profile.id as fromProfileId, message.chatId as chatId,
        (SELECT COUNT(*) FROM ChatMessage WHERE read = 0
        AND fromProfileId = profile.id) as unreadCount,
        CASE :nameOrder
                WHEN 'firstname' THEN
                    TRIM(firstName || ' ' || lastName)
                ELSE
                    TRIM(lastName || ' ' || firstName)
                END AS nameOrdered
        FROM ChatMessage as message
        INNER JOIN PublicProfile as profile ON profile.id = message.chatId
        INNER JOIN Chat as chat ON chat.chatId = message.chatId WHERE isRemove = 0
            AND
                (nameOrdered LIKE '%' || :query || '%' OR phone LIKE '%' || :query || '%')
            GROUP BY chatId
            ORDER BY nameOrdered,
            CASE chatId
            WHEN '549a1006796f014a348b788d' THEN 1
            ELSE 2
        END, max(createDate) DESC