We help IT Professionals succeed at work.

SQL Select Hierarchy

Hello,

I have a table -

CREATE TABLE [dbo].[MyPage](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ParentID] [int] NULL,
	[PageType] [int] NULL,
	[Live] [char](1) NULL,
	[Hidden] [char](1) NULL,
	[DatePublished] [datetime] NULL,
	[EventStartDate] [datetime] NULL,
	[EventStartDateLabel] [nvarchar](100) NULL,
	[EventEndDate] [datetime] NULL,
	[EventEndDateLabel] [nvarchar](100) NULL,
	[UK] [char](1) NULL,
	[Eire] [char](1) NULL,
	[EU] [char](1) NULL,
	[ShareHolderOnly] [char](1) NULL,
	[Title] [nvarchar](100) NULL,
	[SEOLink] [nvarchar](100) NULL,
	[Summary] [nvarchar](350) NULL,
	[Content] [nvarchar](max) NULL,
	[ThumbImage] [nvarchar](150) NULL,
	[MainImage] [nvarchar](150) NULL,
	[Sticky] [char](1) NULL,
	[IncLightbox] [char](1) NULL,
	[Publisher] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Open in new window


I want to run a select statement that orders by ID, ParentID, so that the first record is a record with an ID of 1, the second record returned would be the first of all records with a ParentID of 1...

so it may be that records with IDs of 35,37,39,42 all have ParentIDs of 1, so the resultset would return the rows in the following order -

1, 35, 37, 39, 42, 2, 3, 4....

What would be the best way to do this?

Thanks
Comment
Watch Question

Commented:
You can do it with this.

select * from [dbo].[MyPage]  ORDER BY ParentID, ID ASC

Author

Commented:
Sorry no joy, all I get is 1,2, 3, 4, 35, 37, 39, 42...

Commented:
Can you post a screenshot of the query result? Easier for me, to help you :)

Author

Commented:
Sure thing, here you go --
screenshot

Commented:
You actually want that he doesn't paste the result in following order:

1
2
3
4
5
6
....

But that he places the numbers like you see when you open the table?

Author

Commented:
The ID column would produce the results like so

1
4
5
2
3
6

As rows 4 & 5 have a ParentID of 1, so the records in rows 4 & 5 are related to record 1..

Does that help?
Habib PourfardSoftware Developer
CERTIFIED EXPERT
Top Expert 2012

Commented:
The following query sort ids as you need:
SELECT stuff((SELECT ', ' + cast(ID AS VARCHAR(16))
			  FROM MyPage
			  ORDER BY CASE WHEN ParentID = 0 THEN ID ELSE ParentID END, ID
			  FOR XML PATH ('')), 1, 2, '') IDs

Open in new window

Author

Commented:
Thanks Pourfard,. sorry to ask, but is there anyway of returning these as rows?

Thanks
Software Developer
CERTIFIED EXPERT
Top Expert 2012
Commented:
SELECT ID FROM MyPage
ORDER BY CASE WHEN ParentID = 0 THEN ID ELSE ParentID END, ID

Open in new window

Author

Commented:
Neat - thank you

Explore More ContentExplore courses, solutions, and other research materials related to this topic.