Need help in order by clase of self refrence table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help in order by clase of self refrence table

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-07-16 : 03:05:22

SELECT O.organization_id as OrganizationId,
O.organization_master_id as OrganizationMasterId,
O.organization_name as OrganizationName,
(Select mo.organization_name from organization mo where mo.organization_id = O.organization_master_id) as MasterOrganization,
C.country_name as CountryName,
o.state_province_id,
S.state_province_name as StateProvinceName,
T.city_name as CityName,
o.city_id,
O.address1 as Address1,
O.address2 as Address2,
O.postal_code as PostalCode
FROM country AS C INNER JOIN
organization AS O ON C.country_id = O.country_id LEFT OUTER JOIN
city AS T ON C.country_id = T.country_id AND O.city_id = T.city_id LEFT OUTER JOIN
state_province AS S ON (T.state_province_id = S.state_province_id or C.country_id = S.country_id) AND O.state_province_id = S.state_province_id
where o.active = 1
--order by OrganizationName, CountryName, StateProvinceName ,CityName

organization_master_id, city_name, state_province_name are allowed null
[I need to to suggestion about this query any improvement ?]
[Also I need a better order by clause so that my data should come parent then its child and so on]
for example i have four reocds

Actual Data

OID Oname OMID OMName
1, AA Null Null
2, BB Null Null
3, GG 1 AA
4, DD Null Null
5, EE 1 AA
6, FF 2 BB

Desired Data
OID Oname OMID OMName
1, AA Null Null
5, EE 1 AA
3, GG 1 AA
2, BB Null Null
6, FF 2 BB
4, DD Null Null

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 03:59:40

use:-
order by coalesce(OrganizationMasterId,OrganizationId),OrganizationId

can you give a brief idea about what you're trying to do here?that would help us to rewrite the query if needed.

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-07-16 : 04:17:33

Thanks visakh16
It is good but now problem is for the ordering with organizationname
I have change my order by clause to
order by coalesce( organization_master_id, organization_id),OrganizationId , CountryName, StateProvinceName ,CityName

But now the result is not further sorted by organization name[but it is coming correctly with parent child relation.I need it both]

Please see the following requirement

Data in the Table
OID Oname OMID OMName
2, BB Null Null
3, GG 7 AA
4, DD Null Null
5, EE 7 AA
6, FF 2 BB
7, AA Null Null

Desired Data
OID Oname OMID OMName
7, AA Null Null
5, EE 7 AA
3, GG 7 AA
2, BB Null Null
6, FF 2 BB
4, DD Null Null

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 06:40:04

then replace id with organisationname

order by coalesce( organization_master_id, organization_id),OrganizationName , CountryName, StateProvinceName ,CityName

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-07-16 : 06:51:35

No that doesn't work
All the records mingle up.
Hierarchy with the master then its child [if avaibale] doesn't preserve now.

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 06:54:37

it works for me anyways


declare @test table

(OID int,
 Oname char(2),
OMID int,
 OMName char(2)
)
insert into @test
SELECT 1, 'AA', Null, Null
union all
select 2, 'BB', Null, Null
union all
select 3, 'GG', 1, 'AA'
union all
select 4, 'DD', Null, Null
union all
select 5, 'EE', 1, 'AA'
union all
select 6, 'FF', 2, 'BB'
union all
select 7, 'BC', 1, 'AA'
union all
select 8, 'FF', 2, 'BB'
union all
select 9, 'EE', 4, 'DD'
union all
select 10, 'FF', 2, 'BB'


select *
from @test
order by coalesce(OMID,OID),OName

output
------------------------
OID         Oname OMID        OMName
----------- ----- ----------- ------
1           AA    NULL        NULL
7           BC    1           AA
5           EE    1           AA
3           GG    1           AA
2           BB    NULL        NULL
6           FF    2           BB
8           FF    2           BB
10          FF    2           BB
4           DD    NULL        NULL
9           EE    4           DD

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-07-16 : 07:23:08

U can check

declare @test table

(OID int,
Oname char(2),
OMID int,
OMName char(2)
)
insert into @test
SELECT 11, 'AA', Null, Null
union all
select 2, 'BB', Null, Null
union all
select 3, 'GG', 11, 'AA'
union all
select 4, 'DD', Null, Null
union all
select 5, 'EE', 11, 'AA'
union all
select 6, 'FF', 2, 'BB'
union all
select 7, 'BC', 11, 'AA'
union all
select 8, 'FF', 2, 'BB'
union all
select 9, 'EE', 4, 'DD'
union all
select 10, 'FF', 2, 'BB'

-----------------------
output
------------------------
OID Oname OMID OMName

2 BB NULL NULL
6 FF 2 BB
8 FF 2 BB
10 FF 2 BB
4 DD NULL NULL
9 EE 4 DD
11 AA NULL NULL
7 BC 11 AA
5 EE 11 AA
3 GG 11 AA

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 07:32:42

quote:
Originally posted by kamii47

U can check

declare @test table

(OID int,
Oname char(2),
OMID int,
OMName char(2)
)
insert into @test
SELECT 11, 'AA', Null, Null
union all
select 2, 'BB', Null, Null
union all
select 3, 'GG', 11, 'AA'
union all
select 4, 'DD', Null, Null
union all
select 5, 'EE', 11, 'AA'
union all
select 6, 'FF', 2, 'BB'
union all
select 7, 'BC', 11, 'AA'
union all
select 8, 'FF', 2, 'BB'
union all
select 9, 'EE', 4, 'DD'
union all
select 10, 'FF', 2, 'BB'

-----------------------
output
------------------------
OID Oname OMID OMName

2 BB NULL NULL
6 FF 2 BB
8 FF 2 BB
10 FF 2 BB
4 DD NULL NULL
9 EE 4 DD
11 AA NULL NULL
7 BC 11 AA
5 EE 11 AA
3 GG 11 AA

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com

is this what you asked. First parent info followed by its child in alphabetical order of organisation name??

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-07-16 : 07:44:06

-- Current query Data

OID OMID Oname OmasterName
9 1 NP. PS
3 1 NPS PS
1 NULL PS NULL
8 1 prosltd PS
7 1 pros PS
2 NULL PAL NULL
4 NULL Glaxy checmical NULL
10 4 Glaxy checmical industry Glaxy checmical ----- Data in the Table

OID OMID Oname
1 NULL PS
2 NULL PAL
3 1 NPS
4 NULL Glaxy checmical
7 1 pros
8 1 prosltd
9 1 NP
10 4 Glaxy checmical industry
---Desired Data

OID OMID Oname OmasterName
4 NULL Glaxy checmical NULL
10 4 Glaxy checmical industry Glaxy checmical
2 NULL PAL NULL
1 NULL PS NULL
9 1 NP. PS
3 1 NPS PS
7 1 pros PS
8 1 prosltd PS

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-07-16 : 07:45:18

yes visakh16

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 07:50:17

ok. so you want it by decreasing order of id then just change like this:-

order by coalesce( organization_master_id, organization_id)DESC,OrganizationMasterName,OrganizationName

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-07-16 : 08:10:09

Thanks it worked.
Hats of for you

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 08:14:33

quote:
Originally posted by kamii47

Thanks it worked.
Hats of for you

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com

You're welcome

it always helps when you give some sample data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources