asked on

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

Xaelian

You can do it with this.

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

garethtnash

ASKER

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

Xaelian

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

garethtnash

ASKER

Sure thing, here you go --

Xaelian

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?

garethtnash

ASKER

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 Pourfard

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

garethtnash

ASKER

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

Thanks

ASKER CERTIFIED SOLUTION

Habib Pourfard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

garethtnash

ASKER

Neat - thank you