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

Please, review your answer because i gave additional details. — Ismael, Nov 20 '09 at 18:51

score 4 · Accepted Answer · answered Nov 20 '09 at 19:15

4

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

answered Nov 20 '09 at 19:15

Zote

5,343
5
41
43

1

Yes! that function returned exactly what I wanted. Thanks. – Ismael Nov 20 '09 at 19:18
1

Thanks again, sql version without function was more appropriated for me. – Ismael Nov 20 '09 at 19:30

score 2 · Answer 2 · answered Nov 20 '09 at 18:46

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.

(Years later, oops) adjust that first one's subquery to read `(select Parent_Id from A where Parent_Id is not null)`. Top-level items will have a null ParentId, and having nulls in the subquery's list will mess things up. — Philip Kelley, Feb 26 '14 at 15:02

Jherico · Answer 3 · 2009-11-20T19:11:34.263

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

score 0 · Answer 4 · answered May 04 '21 at 10:38

0

I had to update the query a little to get only child categories, for Postgres 9.4

select count(id) from A as outer_ref where not exists(
    select 1 from A where parent_id=outer_ref.id) and parent_id is not null;

answered May 04 '21 at 10:38

Vasili Pascal

3,102
1
27
21

Get the last children from database

4 Answers4