You can find the detailed answer here.
The short answer is:
Magento load all css assets into asset groups. Asset groups are created based on the css properties and which is what stands crucial for the rendering order of css assets.
An asset property is an an array of attribute values other than src attribute.
Example 1: Without media="all"
Suppose the css are configured in the below order in a layout configuration:
<css src="css/styles-m.css"/>
<css src="css/styles-l.css" media="screen and (min-width: 768px)"/>
<css src="css/print.css" media="print"/>
<css src="Magento_Theme::css/home/slider.css" />
Then the asset grouping will be like this:
1. Asset Group ([]) Holds:
css/styles-m.css
Magento_Theme::css/home/slider.css
2. Asset Group (["media" => "screen and (min-width: 768px)"]) Holds:
css/styles-l.css
3. Asset Group (["media" => "print"]) Holds:
css/print.css
and hence the css rendering order will be:
css/styles-m.css
Magento_Theme::css/home/slider.css
css/styles-l.css
css/print.css
Example 2: With medial="all"
Now if our css are configured in the below order:
<css src="css/styles-m.css"/>
<css src="css/styles-l.css" media="screen and (min-width: 768px)"/>
<css src="css/print.css" media="print"/>
<css src="Magento_Theme::css/home/slider.css" media="all" />
Then the asset grouping will be like this:
1. Asset Group ([]) Holds:
css/styles-m.css
2. Asset Group (["media" => "screen and (min-width: 768px)"]) Holds:
css/styles-l.css
3. Asset Group (["media" => "print"]) Holds:
css/print.css
4. Asset Group (["media" => "all"]) Holds:
Magento_Theme::css/home/slider.css
and hence the css rendering order will be:
css/styles-m.css
css/styles-l.css
css/print.css
Magento_Theme::css/home/slider.css