SQL custom sort

Question

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.

Look at ORDER BY FIELD

Madhur Bhaiya
– Madhur Bhaiya

2018-10-05 18:15:30 +00:00
Commented Oct 5, 2018 at 18:15 — Madhur Bhaiya
– Madhur Bhaiya, Commented Oct 5, 2018 at 18:15

Madhur Bhaiya · Accepted Answer · 2018-10-05 18:23:46Z

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

Gordon Linoff · Accepted Answer · 2018-10-05 18:19:48Z

0

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

answered Oct 5, 2018 at 18:19

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL custom sort

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related