Get the last children from database

sql, sqlanywhere

Solution

Using stored function

create function LastChild(in parent integer)
returns integer
begin
    declare res integer;  
    select top 1 id into res from TableA where parent_id = parent order by timeCol desc;
    return res;
end

select

select Id, lastchild(id) from TAbleA where parent_id is null

I'll work on another solution without stored function.

EDIT: without stored function:

select Id, (select top 1 id from TableA childs where parent_id = TableA.id order by timeCol desc) from TableA where parent_id = 0

Problem

My situation: ``` Table A ( ID Parent_Id TimeStamp ) ``` The root has Parent_Id null and children has Id of its father. I simple want to get all LAST children of every Table A. Father and Children I don't want. (except last one). Is it possible to build a SQL to get this? PS: I'm on sql anywhere 11. Maybe an ansi sql can solve this, i'm not sure. EDIT: (edited to give additional details) I don't want the last children from an element. Example: Id 1 Parent NULL Id 2 Parent 1 Id 3 (the last child) Parent 1 Id 4 Parent NULL Id 5 (the last child) parent 4 I want to get: Id 3 Id 5

Original source