Get the last children from database

Question

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

Answer 1

select * from a where id not in (select parent_id from table a)

In other words, select everything from table a where the ID of the item is not the parent ID of any other item. This will give you all the leaf nodes of the graph.

EDIT:
Your edit is a bit confusing, and ID's aren't typically used as ordering mechanisms, but regardless, the example you give can be accomplished by this query

SELECT MAX( id )
FROM a
WHERE id NOT IN
  (SELECT parent_id
      FROM a
      WHERE parent_id IS NOT NULL
  )
GROUP BY parent_id

Answer 2

If by "last children" you mean items that themselves have no children (and often referred to as leaf-level items), something like this should do:

SELECT ID
 from A
 where ID not in (select Parent_Id from A)

The correlated subquery version is a bit tricker to understand, but would work faster on large tables:

SELECT ID
 from A OuterReference
 where not exists (select 1 from A where Parenti_ID = OuterReference.ID)

("OuterReference" is an alias for table A)

I use SQL Server, but this is pretty basic syntax and should work for you with minimal modification.

Answer 3

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