CODEWITHSUNDEEP
sqlsql-serverparent-child
Ropstah
Ropstah

Reputation: 17804

SQL Server: How to select second-highest parentid?

I have a SQL Server database with these pages:

+------------+--------------+-------------------------------+  
|  pageid    |  parentid    |  title                        |
+------------+--------------+-------------------------------+  
|  1         |  null        |  Home                         |
+------------+--------------+-------------------------------+  
|  2         |  1           |  News                         |
+------------+--------------+-------------------------------+  
|  3         |  1           |  User                         |
+------------+--------------+-------------------------------+  
|  4         |  3           |  Edit profile                 |
+------------+--------------+-------------------------------+  
|  5         |  3           |  Messages                     |
+------------+--------------+-------------------------------+  
|  6         |  5           |  View all                     |
+------------+--------------+-------------------------------+

How do I select the second-highest (in level) parentid for any row? So for pageid=6 (View all) it should return parentid->3 (User).

Upvotes: 0

Views: 345

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294407

For a fixed and known number of steps up the parent hierachy, use explicit joins:

select l2.*
from table t
join table l1 on t.parent_id = l1.pageid
join table l2 on l1.parent_id = l2.pageid
where t.pageid = 6;

For an unknow number of steps in the hierachy, use a recursive cte, but you need a stop criteria, see Recursive Queries Using Common Table Expressions.

Upvotes: 2

Andrew
Andrew

Reputation: 12009

Try:

select max(thing) from table where thing < (select max(thing) from table)

I couldn't pick from your question and your sample whether you want pageid or parentid.

Upvotes: 0

Related Questions