Sorted result Set

Question

Sorted result Set

ramrajan

SSCommitted

Points: 1737
More actions
November 13, 2015 at 8:58 am

#322510

Below is the script for Input table the required result set also posted in a output table
-- column KeyProductControlDisplay is Primary Key where as KeyProductControlDisplayParent tells the parent
-- Step 1 : Foreach group of Parent and Child Records I need to compare Child FormOrder column < Parent Form Order Condition
-- Categorise True / False Records for example take the record KeyProductControlDisplay = 548
10(Child) < 30(Parent) - True
20(Child) < 30(Parent) - True
All The above records should be descended and merged with false record set below
-- False
30(Child) < 30(Parent) - False
CREATE TABLE dbo.Input(
[KeyProductControlDisplay] [int] NOT NULL,
[KeyProductControlDisplayParent] [int] NULL,
[ProductCaption] [nvarchar](85) NULL,
[ComparisonOperationValue] [varchar](255) NULL,
[DefaultSelection] [tinyint] NOT NULL,
[FormOrder] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (526, NULL, N'All Deal Structures', N'0', 1, 0)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (527, 526, N'Equity', N'0', 1, 10)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (528, 527, N'Acquisition Financing', N'256', 1, 10)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (529, 527, N'Follow-on Offering', N'262144', 1, 20)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (530, 527, N'Growth Capital', N'4', 1, 30)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (531, 527, N'IPO', N'65536', 1, 40)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (532, 527, N'Private Investment in Public Equity (PIPE)', N'1024', 1, 50)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (533, 527, N'Private Placement', N'8192', 1, 60)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (534, 527, N'Recapitalization', N'4096', 1, 70)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (535, 526, N'M&A', N'0', 1, 20)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (536, 535, N'Add-on', N'64', 1, 10)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (537, 535, N'Asset Acquisition', N'128', 1, 20)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (538, 535, N'Buyout (LBO)', N'2', 1, 30)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (539, 535, N'Corporate Acquisition', N'16384', 1, 40)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (540, 535, N'Corporate Divestiture', N'512', 1, 50)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (541, 535, N'Growth Capital', N'4', 1, 60)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (542, 535, N'Investor Buyout by Management', N'32768', 1, 70)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (543, 535, N'Management Buy-in (MBI)', N'8', 1, 80)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (544, 535, N'Management Buy-out (MBO)', N'16', 1, 90)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (545, 535, N'Merger', N'131072', 1, 100)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (546, 535, N'Public to Private', N'2048', 1, 110)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (547, 535, N'Secondary Transaction (PE)', N'32', 1, 120)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (548, 526, N'Debt & Other Financings', N'0', 1, 30)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (549, 548, N'Acquisition Financing', N'256', 1, 10)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (550, 548, N'Debt Financing', N'524288', 1, 20)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (551, 548, N'Recapitalization', N'4096', 1, 30)
GO
INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (552, 526, N'Other', N'1', 1, 40)
GO
----
CREATE TABLE dbo.Output(
[KeyProductControlDisplay] [int] NOT NULL,
[KeyProductControlDisplayParent] [int] NULL,
[ProductCaption] [nvarchar](85) NULL,
[ComparisonOperationValue] [varchar](255) NULL,
[DefaultSelection] [tinyint] NOT NULL,
[FormOrder] [int] NOT NULL
) ON [PRIMARY]
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (526, NULL, N'All Deal Structures', N'0', 1, 0)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (527, 526, N'Equity', N'0', 1, 10)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (528, 527, N'Acquisition Financing', N'256', 1, 10)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (529, 527, N'Follow-on Offering', N'262144', 1, 20)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (530, 527, N'Growth Capital', N'4', 1, 30)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (531, 527, N'IPO', N'65536', 1, 40)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (532, 527, N'Private Investment in Public Equity (PIPE)', N'1024', 1, 50)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (533, 527, N'Private Placement', N'8192', 1, 60)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (534, 527, N'Recapitalization', N'4096', 1, 70)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (535, 526, N'M&A', N'0', 1, 20)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (536, 535, N'Add-on', N'64', 1, 10)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (537, 535, N'Asset Acquisition', N'128', 1, 20)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (538, 535, N'Buyout (LBO)', N'2', 1, 30)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (539, 535, N'Corporate Acquisition', N'16384', 1, 40)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (540, 535, N'Corporate Divestiture', N'512', 1, 50)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (541, 535, N'Growth Capital', N'4', 1, 60)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (542, 535, N'Investor Buyout by Management', N'32768', 1, 70)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (543, 535, N'Management Buy-in (MBI)', N'8', 1, 80)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (544, 535, N'Management Buy-out (MBO)', N'16', 1, 90)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (545, 535, N'Merger', N'131072', 1, 100)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (546, 535, N'Public to Private', N'2048', 1, 110)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (547, 535, N'Secondary Transaction (PE)', N'32', 1, 120)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (548, 526, N'Debt & Other Financings', N'0', 1, 30)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (550, 548, N'Debt Financing', N'524288', 1, 20)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (549, 548, N'Acquisition Financing', N'256', 1, 10)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (551, 548, N'Recapitalization', N'4096', 1, 30)
GO
INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (552, 526, N'Other', N'1', 1, 40)
GO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 1

What do you mean by input and output? Are you trying to show the output of the query?

It's unclear what you've done with the data, as the structures are the same. If you are trying to compare rows in the input table with each other, that's different than comparing the two tables.

"above records should be descended and merged" doesn't make sense to me. What does descended mean? When you say merged, how do you merge things? What about other fields?

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

Steve Jones - SSC Editor (11/13/2015)
What do you mean by input and output? Are you trying to show the output of the query?
It's unclear what you've done with the data, as the structures are the same. If you are trying to compare rows in the input table with each other, that's different than comparing the two tables.
"above records should be descended and merged" doesn't make sense to me. What does descended mean? When you say merged, how do you merge things? What about other fields?

I don't understand the question either.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Here's the original post. http://www.sqlservercentral.com/Forums/Topic1735322-391-1.aspx

I've already posted a new possible solution.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2