Use a RECURSIVE CTE
in Postgres:
WITH RECURSIVE cte AS (
SELECT key, value, 1 AS level
FROM taxonomy
WHERE key = 0
UNION ALL
SELECT t.key, t.value, c.level + 1
FROM cte c
JOIN taxonomy t ON t.taxHier = c.key
)
SELECT value
FROM cte
ORDER BY level;
Details and links to documentation in my previous answer:
- Does PostgreSQL have a pseudo-column like “LEVEL” in Oracle?
Related Contents:
- Is it possible to make a recursive SQL query?
- subquery in FROM must have an alias
- Oracle equivalent of Postgres’ DISTINCT ON?
- How do I limit the number of rows returned by an Oracle query after ordering?
- Are PostgreSQL column names case-sensitive?
- PostgreSQL unnest() with element number
- How to select the nth row in a SQL database table?
- What is the major difference between Varchar2 and char
- Return row with the max value of one column per group [duplicate]
- How to list table foreign keys
- ORA-12560: TNS:protocol adaptor error
- Can a foreign key be NULL and/or duplicate?
- Normalize array subscripts so they start with 1
- Delete duplicate rows (don’t delete all duplicate)
- How do I determine the last day of the previous month using PostgreSQL?
- How to use Oracle ORDER BY and ROWNUM correctly?
- Using ‘case expression column’ in where clause
- Is there something like a zip() function in PostgreSQL that combines two arrays?
- How to convert Rows to Columns in Oracle? [duplicate]
- Change PostgreSQL columns used in views
- PostgreSQL multi INSERT…RETURNING with multiple columns
- How to declare variable and use it in the same Oracle SQL script?
- updating table rows in postgres using subquery
- Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
- Finding similar strings with PostgreSQL quickly
- How do you do date math that ignores the year?
- Use email address as primary key?
- PostgreSQL: encoding problems on Windows when using psql command line utility
- SQL column reference “id” is ambiguous
- How to use multiple CTEs in a single SQL query?
- Postgres analogue to CROSS APPLY in SQL Server
- SQL selecting rows by most recent date with two unique columns
- How to Select a substring in Oracle SQL up to a specific character?
- Oracle (Old?) Joins – A tool/script for conversion?
- How do I ALTER a PostgreSQL table and make a column unique?
- Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
- Using pivot on multiple columns of an Oracle row
- create table with sequence.nextval in oracle [duplicate]
- How can you tell if a value is not numeric in Oracle?
- How to anticipate and escape single quote ‘ in oracle
- What is it exactly a BLOB in a DBMS context
- postgresql generate sequence with no gap
- I keep getting the error “relation [TABLE] does not exist”
- Error: Column does not exist
- Does Oracle store trailing zeroes for Number data type?
- calculate hours based on business hours in Oracle SQL
- Aggregating (x,y) coordinate point clouds in PostgreSQL
- Oracle query to match all values in the list among all rows in table
- How do I list all tables in a schema in Oracle SQL?
- Is there a version control system for database structure changes?