Ordering of articles is set within a category. Thus two articles in different categories may have the same order number. If a list of articles covering two or more categories is sorted by order the order displayed is confusing with all the articles with order number 0 coming together, then all the 1s and so on. Makes it difficult to see what the real ordering is.

Steps to reproduce the issue

Create two (or more) content categories: Cat1 Cat2
Create two (or more) articles in each category:
Art1Cat1, Art2Cat1, Art3Cat1, Art4Cat2, Art5Cat2, Art6Cat2
Display the articles view in admin
Filter to show only Cat1
Sort by the ordering column and drag and drop the articles into a new order
say 1=Art2Cat1, 2=Art1Cat1, 3=Art3Cat1
Do the same for Cat2
1=Art6Cat2, 2=Art5Cat2, 3=Art4Cat2
Now clear the filter to display and sort by ordering

Expected result

The desired result would be
Art2Cat1, Art1Cat1, Art3Cat1, Art6Cat2, Art5Cat2, Art4Cat2
in other words they would be shown in sequence within their categories so you could see if one was not where you wanted it and easily drag it to the right place

Actual result

Art2Cat1, Art6Cat2, Art1Cat1, Art5Cat2, Art3Cat1, Art4Cat2
in fact it won't even be as logical as that as all of the articles with order =1 will be together but not necessarily in the same order of categories as all of the ones with order=2
This makes the sorting by order confusing and useless if more than one category is on show.

Sorting by category then order makes it possible to adjust ordering when more than one category is displayed and saves having to switch to a different filtered view.

System information (as much as possible)

Joomla 3.9.2 standard install on Linux kernel 4.4.121-grsec, Apache 2.4.38, php 7.1.26 , MySQL 5.7.25

Additional comments

The fix to improve this is very simple:
In administrator/components/com_content/models/articles.php
at around line 390 in function getListQuery() after

    // Add the list ordering clause.
    $orderCol  = $this->state->get('list.ordering', 'a.id');
    $orderDirn = $this->state->get('list.direction', 'DESC');

add the following

    // if sort by ordering then order by category first
    if ($orderCol=='a.ordering') 
    {
        $orderCol='category_title '.$orderDirn.', a.ordering';
    }

This imposes the same direction on the category order as the ordering order; it could be argued to always make is ASC since the $orderDirn is set for the named column - in this case a.ordering - and it might make more sense not to mess with the preliminary (primary) order in this case.

        $orderCol='category_title ASC, a.ordering';

Could someone competent generate a pull request for this very simple improvement - as far as I can see if doesn't affect anything else.

A secondary improvement would be to add the order number to the ordering sort column alongside the drag and drop icon. (it used to be there before we had drag and drop, simply displaying the number makes it clear what is going on - no need to reintroduce the ability to edit the number there)

In administrator/components/com_content/views/articles/tmpl/default.php
at around line 141 after
<span class="icon-menu" aria-hidden="true"></span>
insert
<?php echo $item->ordering; ?>
before
</span>
This will simply display the order number to the right of the icon, grayed out when the ordering is not active.
Again a very simple improvement - could someone please generate a pull request. You can of course do this with a template override, but I can see no reason not to display the order number to improve clarity of what is happening.

Cheers
RogerCO