Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Select Hierarchy

Posted on 2012-08-13
10
Medium Priority
?
475 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:garethtnash
  • 5
  • 3
  • 2
10 Comments
 
LVL 13

Expert Comment

by:Xaelian
ID: 38287426
You can do it with this.

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

Author Comment

by:garethtnash
ID: 38287444
Sorry no joy, all I get is 1,2, 3, 4, 35, 37, 39, 42...
0
 
LVL 13

Expert Comment

by:Xaelian
ID: 38287451
Can you post a screenshot of the query result? Easier for me, to help you :)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:garethtnash
ID: 38287468
Sure thing, here you go --
screenshot
0
 
LVL 13

Expert Comment

by:Xaelian
ID: 38287552
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?
0
 

Author Comment

by:garethtnash
ID: 38287567
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?
0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38287662
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

0
 

Author Comment

by:garethtnash
ID: 38287793
Thanks Pourfard,. sorry to ask, but is there anyway of returning these as rows?

Thanks
0
 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 2000 total points
ID: 38288076
SELECT ID FROM MyPage
ORDER BY CASE WHEN ParentID = 0 THEN ID ELSE ParentID END, ID

Open in new window

0
 

Author Closing Comment

by:garethtnash
ID: 38288155
Neat - thank you
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question