Search code examples
sqlmysqljoinsql-order-by

SQL sort by 4 columns with 1 column sorted by True and False Both

I'm looking to sort a list of salespersons which needs to be sorted by 4 columns - IsAvailable DESC, EmailStatus DESC, FirstName ASC, LastName ASC.

  • IsAvailable (makes switch button editable) - this means if the salesperson is ready to attend the appointment (sorting - True first then false)
  • EmailStatus (shows red icon if False) - this means if the salesperson has valid mailbox. (sorting - true first then false but when false, the isAvailable should also be false).
  • FirstName and LastName - first name and last name in ASC order

Current sorted list:
enter image description here

Required sorting :
enter image description here

Required sorting list in words:

  1. First it should be Active with IsAvailable True & EmailStatus True & First ASC & Last ASC
  2. Then it should be Active or Inactive with IsAvailable True/False but with EmailStatus False, First and Last ASC
  3. Last should be IsAvailable False and EmailStatus True (which is Inactive switch without the red icon), First and Last ASC

Code I Tried:

SELECT 
    {Collaborator}.*,
    {ShowroomMember}.*,
    {WorkRole}.*,
    (
        SELECT 
            COUNT({ShowroomMember}.[Id])
        FROM 
            {ShowroomMember}
        WHERE 
            {ShowroomMember}.[CollaboratorId] = {Collaborator}.[Id]    
    )
FROM {ShowroomMember}
    INNER JOIN {Collaborator} ON {Collaborator}.[Id] = {ShowroomMember}.[CollaboratorId]
    INNER JOIN {WorkRole} ON {ShowroomMember}.[WorkRoleId] = {WorkRole}.Id
WHERE
    **{ShowroomMember}.[ShowroomId] = @ShowroomId
ORDER BY {ShowroomMember}.[IsAvailable] DESC, {Collaborator}.[EmailStatus] DESC, 
{Collaborator}.[FirstName] ASC, {Collaborator}.[LastName] ASC**
Solution

  • Apologies to the community for using pictures.
    For anyone looking for the solution, I was able to solve this by using CASE. It was a long trial-and-error as the the sorting was not according to solution due to multiple times failing the First Name and Last Name in mixture with IsAvailable and EmailStatus. The code might be ugly but it works. You can update and make it look pretty.

    I've used multiple WHEN with same condition to apply 3 different sorting for same conditions.

    Solution:

    ORDER BY
CASE WHEN {Collaborator}.[EmailStatus]=1 AND {ShowroomMember}.[IsAvailable]=1 THEN {ShowroomMember}.[IsAvailable] END DESC,
CASE WHEN {Collaborator}.[EmailStatus]=1 AND {ShowroomMember}.[IsAvailable]=1 THEN {Collaborator}.[FirstName] END ASC,
CASE WHEN {Collaborator}.[EmailStatus]=1 AND {ShowroomMember}.[IsAvailable]=1 THEN {Collaborator}.[LastName] END ASC,

CASE WHEN {Collaborator}.[EmailStatus]=1 AND {ShowroomMember}.[IsAvailable]=0 THEN {ShowroomMember}.[IsAvailable] END ASC,
CASE WHEN {Collaborator}.[EmailStatus]=1 AND {ShowroomMember}.[IsAvailable]=0 THEN {Collaborator}.[FirstName] END ASC,
CASE WHEN {Collaborator}.[EmailStatus]=1 AND {ShowroomMember}.[IsAvailable]=0 THEN {Collaborator}.[LastName] END ASC,

CASE
    WHEN {Collaborator}.[EmailStatus]=0 AND ({ShowroomMember}.[IsAvailable]=0 OR {ShowroomMember}.[IsAvailable]=1)
    THEN {Collaborator}.[FirstName] END ASC

    The Last CASE holds the sorting of salesperson in the list when changing the status from inactive to active or vice-versa. If I would use the CASE like the I used for other conditions in the code above, it would sort it differently, hence I had to combine the True and False of IsAvailable together using OR to preserve the sorting of the salesperson according to the first and last name.