Just add two conditions on the previous query
A particularity of this approach is that the non-null record will always appear with the first child in alphabetical order.
But, according to what you explain, I guess it is OK, and if it doesn't it is easily changed. :-)
Also I've changed the first condition on the right outer join coz it would give trouble if two childs happened to be of the same age.
I guess it will never happen two childs with the same name :p
Best,
Oliveiros
SELECT a.num,a.father,b.child,b.age
FROM
(
SELECT num,father, MIN(child)as primeira
FROM t1
GROUP BY num,father) a
RIGHT JOIN t1 b
ON b.child = a.primeira
AND b.father = a.father
AND a.num = b.num
--
even the biggest failure, even the worst mistake, beats the hell out of never trying...
- Meredith Grey
2008/10/23 Zied Kharrat
<khzied@gmail.com>hi,
i don't want any sort.. just like this example
num father child age col5
1 joe bruce 14 8
lei 10
mike 5
2 manuel child1 35 16
child2 33
child3 30
this is what i want really..
What can be then the sql request without sort with this update.. Thanks :)
