MySQL Forums
Forum List  »  Newbie

Unexpected ordering of query results
Posted by: Karen x
Date: April 18, 2019 09:18PM

MySQL Community Server 8.0.15

The following query...

mysql> SELECT employee.emp_id, employee.fname, employee.lname, department.name dept_name
-> FROM employee INNER JOIN department
-> ON employee.dept_id = department.dept_id;

...produced what seems to me some strangely-ordered results:

+--------+----------+-----------+----------------+
| emp_id | fname | lname | dept_name |
+--------+----------+-----------+----------------+
| 4 | Susan | Hawthorne | Operations |
| 6 | Helen | Fleming | Operations |
| 7 | Chris | Tucker | Operations |
| 8 | Sarah | Parker | Operations |
| 9 | Jane | Grossman | Operations |
| 10 | Paula | Roberts | Operations |
| 11 | Thomas | Ziegler | Operations |
| 12 | Samantha | Jameson | Operations |
| 13 | John | Blake | Operations |
| 14 | Cindy | Mason | Operations |
| 15 | Frank | Portman | Operations |
| 16 | Theresa | Markham | Operations |
| 17 | Beth | Fowler | Operations |
| 18 | Rick | Tulman | Operations |
| 5 | John | Gooding | Loans |
| 1 | Michael | Smith | Administration |
| 2 | Susan | Barker | Administration |
| 3 | Robert | Tyler | Administration |
+--------+----------+-----------+----------------+
18 rows in set (0.00 sec)

I was expecting this to be ordered by emp_id in ascending order. Instead, it appears to be sorted first by department (in descending order) and then by employee ID (in ascending order). Yet nothing inherent in the query seems to me to dictate this.

Why would this query produce this result? The book I am working from (O'Reilly's "Learning SQL") shows the result set for this query listed in ascending order by employee ID.

In order for me to achieve the same result, I had to change my query:


mysql> SELECT employee.emp_id, employee.fname, employee.lname, department.name dept_name
-> FROM employee INNER JOIN department
-> ON employee.dept_id = department.dept_id
-> ORDER BY employee.emp_id;
+--------+----------+-----------+----------------+
| emp_id | fname | lname | dept_name |
+--------+----------+-----------+----------------+
| 1 | Michael | Smith | Administration |
| 2 | Susan | Barker | Administration |
| 3 | Robert | Tyler | Administration |
| 4 | Susan | Hawthorne | Operations |
| 5 | John | Gooding | Loans |
| 6 | Helen | Fleming | Operations |
| 7 | Chris | Tucker | Operations |
| 8 | Sarah | Parker | Operations |
| 9 | Jane | Grossman | Operations |
| 10 | Paula | Roberts | Operations |
| 11 | Thomas | Ziegler | Operations |
| 12 | Samantha | Jameson | Operations |
| 13 | John | Blake | Operations |
| 14 | Cindy | Mason | Operations |
| 15 | Frank | Portman | Operations |
| 16 | Theresa | Markham | Operations |
| 17 | Beth | Fowler | Operations |
| 18 | Rick | Tulman | Operations |
+--------+----------+-----------+----------------+
18 rows in set (0.00 sec)


Has something changed in MySQL since this book was published?


mysql> SELECT *
-> FROM employee;
+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+
| emp_id | fname | lname | start_date | end_date | superior_emp_id | dept_id | title | assigned_branch_id |
+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+
| 1 | Michael | Smith | 2001-06-22 | NULL | NULL | 3 | President | 1 |
| 2 | Susan | Barker | 2002-09-12 | NULL | 1 | 3 | Vice President | 1 |
| 3 | Robert | Tyler | 2000-02-09 | NULL | 1 | 3 | Treasurer | 1 |
| 4 | Susan | Hawthorne | 2002-04-24 | NULL | 3 | 1 | Operations Manager | 1 |
| 5 | John | Gooding | 2003-11-14 | NULL | 4 | 2 | Loan Manager | 1 |
| 6 | Helen | Fleming | 2004-03-17 | NULL | 4 | 1 | Head Teller | 1 |
| 7 | Chris | Tucker | 2004-09-15 | NULL | 6 | 1 | Teller | 1 |
| 8 | Sarah | Parker | 2002-12-02 | NULL | 6 | 1 | Teller | 1 |
| 9 | Jane | Grossman | 2002-05-03 | NULL | 6 | 1 | Teller | 1 |
| 10 | Paula | Roberts | 2002-07-27 | NULL | 4 | 1 | Head Teller | 2 |
| 11 | Thomas | Ziegler | 2000-10-23 | NULL | 10 | 1 | Teller | 2 |
| 12 | Samantha | Jameson | 2003-01-08 | NULL | 10 | 1 | Teller | 2 |
| 13 | John | Blake | 2000-05-11 | NULL | 4 | 1 | Head Teller | 3 |
| 14 | Cindy | Mason | 2002-08-09 | NULL | 13 | 1 | Teller | 3 |
| 15 | Frank | Portman | 2003-04-01 | NULL | 13 | 1 | Teller | 3 |
| 16 | Theresa | Markham | 2001-03-15 | NULL | 4 | 1 | Head Teller | 4 |
| 17 | Beth | Fowler | 2002-06-29 | NULL | 16 | 1 | Teller | 4 |
| 18 | Rick | Tulman | 2002-12-12 | NULL | 16 | 1 | Teller | 4 |
+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+
18 rows in set (0.02 sec)




mysql> SELECT *
-> FROM department;
+---------+----------------+
| dept_id | name |
+---------+----------------+
| 1 | Operations |
| 2 | Loans |
| 3 | Administration |
+---------+----------------+
3 rows in set (0.00 sec)


(Yes, I am aware I can use column aliases, but I'm just typing in the query as it appeared at that point in the book. Aliases had not yet been covered.)

Options: ReplyQuote


Subject
Written By
Posted
Unexpected ordering of query results
April 18, 2019 09:18PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.