views:

66

answers:

2

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).

A: 

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.

Andrew
A good answer, but for a different question.This post concerns a tree hierachy, and the question is asking how to find the parent of a node's parent.
Andrew Shepherd
+2  A: 

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.

Remus Rusanu
Whoops, i guess this is exactly what i need. However I don't understand any bit of how this works... Also I'm using the express version of sql server. Might there be a more simple solution?
Ropstah