There are two SQL tables:
Parents:
+--+---------+
|id| text |
+--+---------+
| 1| Blah |
| 2| Blah2 |
| 3| Blah3 |
+--+---------+
Childs
+--+------+-------+
|id|parent|feature|
+--+------+-------+
| 1| 1 | 123 |
| 2| 1 | 35 |
| 3| 2 | 15 |
+--+------+-------+
I want to select with single query every row from Parents table and for each one single row from Childs table with relation "parent"-"id" value and the greatest "feature" column value. In this example result should be:
+----+------+----+--------+---------+
|p.id|p.text|c.id|c.parent|c.feature|
+----+------+----+--------+---------+
| 1 | Blah | 1 | 1 | 123 |
| 2 | Blah2| 3 | 2 | 15 |
| 3 | Blah3|null| null | null |
+----+------+----+--------+---------+
Where p = Parent table and c = Child table
I tried to LEFT OUTER JOIN and GROUP BY but MSSQL Express told me that query with GROUP BY require Aggregate functions on every non-Groupped fields. And I do not want to Group them all, but rather select top row (with custom ordering).
I am totally out of ideas...