We help IT Professionals succeed at work.

Sort - parent records but keep child records "under" them

I have the below results - a list of child records under each associative parent - what I would like to so is sort the parents first by alpha ASC, but still keep all of the children under their associated parent

Current Results
Comment
Watch Question

Author

Commented:
Here is my current query:

 
SELECT [contractMasterID], [Contract ID],[Contract],[Contract Parent], sortID, totalCharge  FROM (
SELECT 
contractID AS [Contract ID]
,CASE 
	WHEN isParent = 1 THEN InsuranceCompanyName + ' - Parent'
	ELSE contractNumber + ' - ' + InsuranceCompanyName END AS [Contract],
CASE WHEN ContractMasterID IS NOT NULL AND isParent IS NULL or isParent = 0 
	 THEN (SELECT '('+ CAST(contractID AS varchar(30))+ ') ' + InsuranceCompanyName + ' - Parent' FROM cm_tblContractSummary x WHERE x.contractID = c.contractMasterID)
	 WHEN isParent = 1 THEN 'Parent' ELSE 'No Parent Assigned'END AS [Contract Parent]
,contractMasterID
,1 AS sortID
,SUM(ISNULL(defaultINpatientCharge,0) + ISNULL(defaultOutpatientCharge,0) + ISNULL(defaultEmergencyCharge,0)
	+ ISNULL(defaultUrgentCareCharge,0) + ISNULL(defaultNonPatientCharge,0) + ISNULL(defaultAmbulatoryCharge,0)) AS [totalCharge]

FROM cm_tblContractSummary c
WHERE db = 'dbAlaskaNativePhysicn'
GROUP BY contractID, isParent, insuranceCompanyName, contractNumber, contractMasterID

) z
WHERE [Contract Parent] <> 'No Parent Assigned'
ORDER BY [contractMasterID],  
CASE WHEN [Contract Parent] <> 'Parent' THEN Contract ELSE CAST([Contract ID] AS varchar(20)) END

Open in new window

Commented:
You'll need to artificially create a sort field that can be sorted properly

Using your above example,

select
     contractMasterID,
     ContractID,
     Contract,
     [Contract Parent],
     SortID,
     Cast(Contract as varchar(500)) + cast(ContractMasterID as varchar(50)) + cast(ContractID as varchar(50)) as MyCompositeKey
Order by MyCompositKey

Depending on data min/max you may need to push contractMasterID and ContractID into a space filled right set condition to get what you need.


Commented:
This might help

CREATE FUNCTION [dbo].[ZeroFillRightSet] (
	@Valu int,
	@CharsWide int
)
RETURNS varchar(20)
AS
BEGIN
	DECLARE @Ret varchar(20),
			@BigInt bigint 
	select @BigInt= (@Valu + 100000000000000000)
	select @Ret = cast(@BigInt as varchar(20))
	select @Ret = RIGHT(@Ret, @CharsWide)
	RETURN @Ret
END
  

Open in new window

Commented:
so ...
select
     contractMasterID,
     ContractID,
     Contract,
     [Contract Parent],
     SortID,
     Cast(Contract as varchar(500)) + cast(ContractMasterID as varchar(50)) + cast(ContractID as varchar(50)) as MyCompositeKey
Order by MyCompositKey

becomes

select
     contractMasterID,
     ContractID,
     Contract,
     [Contract Parent],
     SortID,
     Cast(Contract as varchar(500)) + dbo.ZeroFillRightSet(ContractMasterID, 10) + dbo.ZeroFillRightSet(ContractID, 10) as MyCompositeKey
Order by MyCompositKey

Commented:
And using your snippet ...

SELECT [contractMasterID], [Contract ID],[Contract],[Contract Parent], sortID, totalCharge,
	Cast(Contract as varchar(500)) + dbo.ZeroFillRightSet(ContractMasterID, 10) + dbo.ZeroFillRightSet(ContractID, 10) as MyCompositeKey
			FROM (
			SELECT 
			contractID AS [Contract ID]
			,CASE 
				WHEN isParent = 1 THEN InsuranceCompanyName + ' - Parent'
				ELSE contractNumber + ' - ' + InsuranceCompanyName END AS [Contract],
			CASE WHEN ContractMasterID IS NOT NULL AND isParent IS NULL or isParent = 0 
				 THEN (SELECT '('+ CAST(contractID AS varchar(30))+ ') ' + InsuranceCompanyName + ' - Parent' FROM cm_tblContractSummary x WHERE x.contractID = c.contractMasterID)
				 WHEN isParent = 1 THEN 'Parent' ELSE 'No Parent Assigned'END AS [Contract Parent]
			,contractMasterID
			,1 AS sortID
			,SUM(ISNULL(defaultINpatientCharge,0) + ISNULL(defaultOutpatientCharge,0) + ISNULL(defaultEmergencyCharge,0)
				+ ISNULL(defaultUrgentCareCharge,0) + ISNULL(defaultNonPatientCharge,0) + ISNULL(defaultAmbulatoryCharge,0)) AS [totalCharge]

			FROM cm_tblContractSummary c
			WHERE db = 'dbAlaskaNativePhysicn'
			GROUP BY contractID, isParent, insuranceCompanyName, contractNumber, contractMasterID

			) z
WHERE [Contract Parent] <> 'No Parent Assigned'
ORDER BY MyCompositeKey

Open in new window


Author

Commented:
Using that approach - you can see from the below - the parents are sorted, but the children, sharing the same contractMasterID, are not under each parent respectively

results

Commented:
Including ContractMasterID and ContractID in the sort field prevents two like named contracts from sorting as a single. For example, if you had two AETNA's, one with ContractMasterID = 123 and ther other with ContractMasterID = 345 the resultant sort fields would be ...

AETNA00000123###ContractID###
and
AETNA00000345###ContractID###

Commented:
May I see the TSQL which produced above result?

Author

Commented:
SELECT [contractMasterID], [Contract ID],[Contract],[Contract Parent], sortID, totalCharge,
      Cast(Contract as varchar(500)) + dbo.ZeroFillRightSet(ContractMasterID, 10) + dbo.ZeroFillRightSet([Contract ID], 10) as MyCompositeKey
                  FROM (
                  SELECT
                  contractID AS [Contract ID]
                  ,CASE
                        WHEN isParent = 1 THEN InsuranceCompanyName + ' - Parent'
                        ELSE contractNumber + ' - ' + InsuranceCompanyName END AS [Contract],
                  CASE WHEN ContractMasterID IS NOT NULL AND isParent IS NULL or isParent = 0
                         THEN (SELECT '('+ CAST(contractID AS varchar(30))+ ') ' + InsuranceCompanyName + ' - Parent' FROM cm_tblContractSummary x WHERE x.contractID = c.contractMasterID)
                         WHEN isParent = 1 THEN 'Parent' ELSE 'No Parent Assigned'END AS [Contract Parent]
                  ,contractMasterID
                  ,1 AS sortID
                  ,SUM(ISNULL(defaultINpatientCharge,0) + ISNULL(defaultOutpatientCharge,0) + ISNULL(defaultEmergencyCharge,0)
                        + ISNULL(defaultUrgentCareCharge,0) + ISNULL(defaultNonPatientCharge,0) + ISNULL(defaultAmbulatoryCharge,0)) AS [totalCharge]

                  FROM cm_tblContractSummary c
                  WHERE db = 'dbAlaskaNativePhysicn'
                  GROUP BY contractID, isParent, insuranceCompanyName, contractNumber, contractMasterID

                  ) z
WHERE [Contract Parent] <> 'No Parent Assigned'
ORDER BY MyCompositeKey

Commented:
hmmmmm got it
how many rows does this query return? 10 million? I hope not, because you're going to need a query that references itself.

Give me a couple of minutes to work up a sample.

Author

Commented:
usually less than 1000 total rows

Commented:
First off ... remember ... I do  NOT have an underlying DB to test this on (grin) meaning there may be syntax errors.

	CREATE TABLE #LocTable 
	(
		[contractMasterID] int,
		[Contract ID] int,
		[Contract] varchar(500),
		[Contract Parent] varchar(500), 
		sortID int, 
		totalCharge money, -- not sure this is right type
		CompositeKey varchar(1000)
	)
	CREATE UNIQUE CLUSTERED INDEX IX_1 on #LocTable (contractMasterID)

insert ([contractMasterID], [Contract ID], [Contract], [Contract Parent], sortID, totalCharge)
	SELECT [contractMasterID], [Contract ID],[Contract],[Contract Parent], sortID, totalCharge,
		FROM (
		SELECT 
		contractID AS [Contract ID]
		,CASE 
			WHEN isParent = 1 THEN InsuranceCompanyName + ' - Parent'
			ELSE contractNumber + ' - ' + InsuranceCompanyName END AS [Contract],
		CASE WHEN ContractMasterID IS NOT NULL AND isParent IS NULL or isParent = 0 
			 THEN (SELECT '('+ CAST(contractID AS varchar(30))+ ') ' + InsuranceCompanyName + ' - Parent' FROM cm_tblContractSummary x WHERE x.contractID = c.contractMasterID)
			 WHEN isParent = 1 THEN 'Parent' ELSE 'No Parent Assigned'END AS [Contract Parent]
		,contractMasterID
		,1 AS sortID
		,SUM(ISNULL(defaultINpatientCharge,0) + ISNULL(defaultOutpatientCharge,0) + ISNULL(defaultEmergencyCharge,0)
			+ ISNULL(defaultUrgentCareCharge,0) + ISNULL(defaultNonPatientCharge,0) + ISNULL(defaultAmbulatoryCharge,0)) AS [totalCharge]

		FROM cm_tblContractSummary c
		WHERE db = 'dbAlaskaNativePhysicn'
		GROUP BY contractID, isParent, insuranceCompanyName, contractNumber, contractMasterID

		) z
	WHERE [Contract Parent] <> 'No Parent Assigned'

update a
	set   a.CompositeKey = 
		Cast(b.Contract as varchar(500)) + 
		dbo.ZeroFillRightSet(a.ContractMasterID, 10) + 
		dbo.ZeroFillRightSet(a.ContractID, 10)
	from #LocTable a
		inner join #LocTable b 
		on a.contractMasterID = b.contractMasterID
		and b.[Contract Parent]='Parent'

Select * from #LocTable order by CompositeKey

Open in new window

Commented:
Told ya! (smile)
Line 13 should read

insert #LocTable ([contractMasterID], [Contract ID], [Contract], [Contract Parent], sortID, totalCharge)

Author

Commented:
I think that is really close - but is there any way to get the "parent" at the top of the list - seems to be sorting ok - but the parent is at the bottom of the childre
 sample

Commented:
and ...
line 11 may need to read

CREATE INDEX IX_1 on #LocTable (contractMasterID)

Commented:
Wait one ...

Commented:
Try this

	CREATE TABLE #LocTable 
	(
		[contractMasterID] int,
		[Contract ID] int,
		[Contract] varchar(500),
		[Contract Parent] varchar(500), 
		sortID int, 
		totalCharge money, -- not sure this is right type
		CompositeKey varchar(1000),
		IsParent bit
	)
	CREATE INDEX IX_1 on #LocTable (contractMasterID)

insert #LocTable([contractMasterID], [Contract ID], [Contract], [Contract Parent], sortID, totalCharge)
	SELECT [contractMasterID], [Contract ID],[Contract],[Contract Parent], sortID, totalCharge,
		FROM (
		SELECT 
		contractID AS [Contract ID]
		,CASE 
			WHEN isParent = 1 THEN InsuranceCompanyName + ' - Parent'
			ELSE contractNumber + ' - ' + InsuranceCompanyName END AS [Contract],
		CASE WHEN ContractMasterID IS NOT NULL AND isParent IS NULL or isParent = 0 
			 THEN (SELECT '('+ CAST(contractID AS varchar(30))+ ') ' + InsuranceCompanyName + ' - Parent' FROM cm_tblContractSummary x WHERE x.contractID = c.contractMasterID)
			 WHEN isParent = 1 THEN 'Parent' ELSE 'No Parent Assigned'END AS [Contract Parent]
		,contractMasterID
		,1 AS sortID
		,SUM(ISNULL(defaultINpatientCharge,0) + ISNULL(defaultOutpatientCharge,0) + ISNULL(defaultEmergencyCharge,0)
			+ ISNULL(defaultUrgentCareCharge,0) + ISNULL(defaultNonPatientCharge,0) + ISNULL(defaultAmbulatoryCharge,0)) AS [totalCharge]

		FROM cm_tblContractSummary c
		WHERE db = 'dbAlaskaNativePhysicn'
		GROUP BY contractID, isParent, insuranceCompanyName, contractNumber, contractMasterID

		) z
	WHERE [Contract Parent] <> 'No Parent Assigned'

update a
	set   a.CompositeKey = 
		Cast(b.Contract as varchar(500)) + 
		dbo.ZeroFillRightSet(a.ContractMasterID, 10) + 
		dbo.ZeroFillRightSet(a.ContractID, 10)
	from #LocTable a
		inner join #LocTable b 
		on a.contractMasterID = b.contractMasterID
		and b.[Contract Parent]='Parent'

Update #LocTable set 
	IsParent = CASE 
     WHEN [Contract Parent]='Parent' THEN 1
     ELSE 0
	END

Select * from #LocTable order by IsParent desc, CompositeKey

Open in new window

Commented:
opps - line 53 should be reversed

Select * from #LocTable order by CompositeKey, IsParent desc

Author

Commented:
Whoops - not sure if I was clear - instead of (in previous post) - which essentially works - but instead of:

child
child
parent

I wanted it:

parent
child
child

Commented:
By The Way ... looks like you work for an insurance company - What da HECK you doing working at THIS time of night?

Commented:
Yeah - try my post

opps - line 53 should be reversed

Select * from #LocTable order by CompositeKey, IsParent desc

Author

Commented:
slaving away on a Friday night for a healthcare company actually - tons of fun....

the last query - even with the updated last line - produces the following - as you can see - parents at the bottom of the children - but the parents are sorting as expected
 results

Commented:
Okay ... question
If the PARENT is moved to the TOP, what ORDER should the underlying children take?
numeric [Contract ID] or
alpha [Contract]

Author

Commented:
alpha [Contract]
Commented:
Moved IsParent update above CompositKey build then included IsParent in CompositeKey  build (and changed ContractID to Contract)

Update #LocTable set 
	IsParent = CASE 
     WHEN [Contract Parent]='Parent' THEN 0
     ELSE 1
	END

update a
	set   a.CompositeKey = 
		Cast(b.Contract as varchar(500)) + 
		dbo.ZeroFillRightSet(a.ContractMasterID, 10) + 
		cast(IsParent as varchar(5)) + 
		cast(a.[Contract] as varchar(500)
	from #LocTable a
		inner join #LocTable b 
		on a.contractMasterID = b.contractMasterID
		and b.[Contract Parent]='Parent'

Select * from #LocTable order by CompositeKey

Open in new window

Commented:
Oh, also reversed IsParent update from 1-0 to 0-1 to make sure it sorts right

Commented:
Opps - line 12 is missing a paren

            cast(a.[Contract] as varchar(500))

Author

Commented:
WOW!  Awesome!  Thanks so much!  Works perfectly!  Saved my bacon and lets me go home finally... Thanks!

Commented:
Welcome ...
I use this technique a lot, create an intermediate table, then slice-n-dice the intermediate table a dozen ways to sunday.

Later
I'm gonna go eat dinner!

Author

Commented:
Learned something new tonight - much thanks!