CODEWITHSUNDEEP
sqlsorting
Maghoumi
Maghoumi

Reputation: 3380

Query to group rows together then sort in those groups

The question might sound a little weird. I have a Pages table as follows: 

Id    PageName    Parent    Predecessor  
1     Home        null      null  
2     About       null      1  
3     Contact me  2         null  
4     My Resume   2         3

So it is basically like:

Home  
About  
|--Contact me  
|--My resume

I want to write a select query that will first "group" pages based on their Parent then sort in those groups based on the value of their Predecessor. That is preserve the tree structure of those pages. Needless to say, Parent and Predecessor are foreign keys pointing to Id in the same Pages table.
Any help is greatly appreciated :-) Thanks in advance
Edit 1: I'm using SQL Server
Edit 2: Here's an actual picture:

enter image description here

It should show "Contact Me" below About (as the Parent field indicates)
NOTE: In this sample, Order is not a foreign key. It's just an int which is used for sorting.

Upvotes: 0

Views: 168

Answers (2)

jit
jit

Reputation: 1626

Something like below?

SELECT t1.*,t2.* FROM test t1 LEFT JOIN test t2 
ON t1.id=t2.parent
WHERE t1.parent IS NULL
ORDER BY t1.id,t2.id

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this

select PageName from Pages 
order by isnull(Parent,0),ISNULL(Predecessor,0)

Upvotes: 1

Related Questions