CODEWITHSUNDEEP
mysqlsql
lohitaksh yogi
lohitaksh yogi

Reputation: 111

SQL custom sort

I have a table named Tasks as shown below. Fields in the table are employees, tasks, and date.

Input table :

Date:       employees   Task
2016-12-03  John    Paint the walls pink
2016-12-03  Sam     Play hockey on linoleum
2016-12-03  John    the golden fish in a bucket
2016-12-03  Sam     Create a rink on the porch
2016-12-03  Nathan  Glue the front door
2016-12-03  Nathan  Paint the walls pink
2016-12-08  Sam     Melt the doorknob
2016-12-08  Dewey   Wrap the cat in toilet paper
2016-12-08  John    Give bubble gum to the dog
2016-12-08  Dewey   Order 20 pizzas
2016-12-09  John    Create a rink on the porch
2016-12-09  Nathan  Eat all the candies
.......
...... so on

For Output, I want to select a list which would be sorted according to date first and then according to specific Order of employee (John come first, Nathan second, sam Third, Dewey fourth).

Output:
    Date:       employees   Task
    2016-12-03  John    Paint the walls pink
    2016-12-03  John    the golden fish in a bucket
    2016-12-03  sam     Play hockey on linoleum
    2016-12-03  Sam     Create a rink on the porch
    2016-12-03  Nathan  Glue the front door

..
..
.... so on.

I tried using ORDER BY. How can I get the Sorting done in the specific sequence as mentioned above? Or do I need to know some other concept? Any help would be appreciated.

Upvotes: 2

Views: 38

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You can use FIELD() function. Details of the function are: 

FIELD(str,str1,str2,str3,...)

Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

Now, the FIELD(employees, 'John', 'Nathan', 'Sam', 'Dewey') function will return 0 if the employees is not in ('John', 'Nathan', 'Sam', 'Dewey').

So, if we use ORDER BY FIELD(employees, 'John', 'Nathan', 'Sam', 'Dewey') only, other non-matching rows will appear at the top. So, we can use If() function to return 1 for the other non-matching rows, and 0 for the matched rows.

Now, we can utilize one more level of ordering by FIELD(employees, 'John', 'Nathan', 'Sam', 'Dewey'). This will ensure that the matched id(s) appear first in the order as specified in the Field() function.

Try:

SELECT * 
FROM your_table 
ORDER BY `date` ASC, 
          IF(FIELD(employees, 'John', 'Nathan', 'Sam', 'Dewey') = 0, 1, 0) ASC, 
          FIELD(employees, 'John', 'Nathan', 'Sam', 'Dewey') ASC

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use the FIELD() function:

SELECT * 
FROM your_table 
ORDER BY `date` ASC, FIELD(employees, 'John', 'Nathan', 'Sam', 'Dewey')

The only caveat is if you have other employees. The above logic will put them first.

So, you might want something like:

SELECT * 
FROM your_table 
ORDER BY `date` ASC,
          COALESCE(NULLIF(FIELD(employees, 'John', 'Nathan', 'Sam', 'Dewey'), 0), 999999)

I'm using the COALESCE(NULLIF()) combination to avoid repeating the list of employees. Another alternative is the reverse list:

SELECT * 
FROM your_table 
ORDER BY `date` ASC,
          FIELD(employees, 'Dewey', 'Sam', 'Nathan', 'John') DESC

Upvotes: 0

Related Questions