Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL : how to find leaf rows?

i have a self related table myTable like :

ID | RefID
----------
 1 | NULL
 2 | 1
 3 | 2
 4 | NULL
 5 | 2
 6 | 5
 7 | 5
 8 | NULL
 9 | 7

i need to get leaf rows on any depth

based on the table above, the result must be :

ID | RefID
----------
 3 | 2
 4 | NULL
 6 | 5
 8 | NULL
 9 | 7

thank you

PS: the depth may vary , here is very small example

Here is a visual demonstration of the sample data

like image 565
armen Avatar asked May 20 '13 15:05

armen


People also ask

How do I find the rows in a SQL table?

Use the COUNT aggregate function to count the number of rows in a table. This function takes the name of the column as its argument (e.g., id ) and returns the number of rows for this particular column in the table (e.g., 5).

What is leaf level page in SQL Server?

The leaf level is the level just above the data; it contains one index row for each data row. Index rows on the index page are stored in key value order.

What is leaf node in SQL?

In Sql Server an index is made up of a set of pages (index nodes) that are organized in a B+ tree structure. This structure is hierarchical in nature. The top node is called the ROOT node (Root Page). The bottom level of nodes in the index are called the leaf nodes (leaf Pages).

What is leaf node index?

Each leaf node is stored in a database block or page; that is, the database's smallest storage unit. All index blocks are of the same size—typically a few kilobytes. The database uses the space in each block to the extent possible and stores as many index entries as possible in each block.


1 Answers

Try:

SELECT id,
       refid
FROM   mytable t
WHERE  NOT EXISTS (SELECT 1
                   FROM   mytable
                   WHERE  refid = t.id)  
like image 179
xlecoustillier Avatar answered Oct 04 '22 20:10

xlecoustillier