Subselect in ORDER BY? Valid SQL?

Question

Is this valid SQL? If yes, could you please tell me what it does?

Select *
from MyFirstTable
order by (select min(somefield)
    from MySecondTable
    where MyFirstTable.id = MySecondTable.id)

A subselect in an "order by", how is that possible?? In effect this SQL query does not sort by a field, but by some value in a row of a field (min). It does not seem logical so sort by anything else other than a field name. But min(somefield) <> somefield! But, yes, this query works and someone at work who teaches me told me this, and i'm sceptical. Can you tell me what this means? Or just post an equivilant query?

Thanks!

Answer 1

This query orders MyFirstTable by the minimum value of somefield stored in MySecondTable under the same id.

Here's a quick example:

MyFirstTable
id
1
2
3

MySecondTable
id    somefield
1     2
1     4
2     1
3     6
3     4

In the above case, your query would return

id
2
1
3

Answer 2

An equivalent query that may make more sense:

SELECT MyFirstTable.ID, MyFirstTable.A, MyFirstTable.B
FROM MyFirstTable
INNER JOIN MySecondTable ON MyFirstTable.ID = MySecondTable.ID
GROUP BY MyFirstTable.ID, MyFirstTable.A, MyFirstTable.B
ORDER BY MIN(MySecondTable.SomeField)