June 10, 2012 at 10:23 am
Here is a scenario.
mytable has columns "empid", "emptype" and "managerID"
it has millions of rows.
when i want to know the hierarchy chart, i would obviously look based on "emptype". so, i thought of using CTE.
i dont want the hierarchy of each employee, but just for Emptype - so, a single instance of hierarchy, an organizational chart.
i tried this way, but keep executing as the table has millions of rows.
// <TOP 1> IS ADDED HERE TO SHOW MY REQUIREMENT.
with cte(EventID, EventType, EventName, ParentEventType, ParentEventName)
AS
(
select TOP 1 child.EventID, child.EventType, child.EventName, parent.eventtype, parent.eventname from vaapps.tevent child inner join vaapps.tevent parent on child.parenteventid = parent.eventid
where parent.parenteventid is NULL
union all
select TOP 1 child.EventID, child.EventType, child.EventName, parent.EventType, parent.EventName
from vaapps.tevent child inner join cte parent
on child.parenteventid = parent.EventID
)
select EventType, EventName, ParentEventType, ParentEventName from cte
June 10, 2012 at 11:07 am
Top 1 without order by? So you just want any random row from the table? Doesn't really make much sense....
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2012 at 12:57 am
GilaMonster (6/10/2012)
Top 1 without order by? So you just want any random row from the table? Doesn't really make much sense....Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
yes. i want any random, as i need any one from each hierarchy
eg. assume hierarchies like loadman, supervisor, manager
mytable will have many rows in same level,
but
i need 1 from manager level to know this is the highest level,
i need 1 from supervisor level to know this is the second level,
i need 1 from loadman level to know this is the first level.
So, finally, i will get three rows with columns "level" and "parentlevel" which satisfy my requirement.
June 11, 2012 at 1:13 am
-
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 11, 2012 at 1:28 am
Create a non clustered index on emptype.
Create table #emptype (id int identity(1,1), emptype varchar(20), empid_eg int)
Insert into #emptype
Select distinct emptype, null from tablename
Update #emptype
Select empid_eg = max(a.empid)
from #emptype inner join table a
group by a.emptype
Use cte to traverse the heirachy join to #emptype on empid = empid_eg
Probably better ways to do it though
June 11, 2012 at 1:42 am
Did you try using it with level indication ?
;
with cte ( EventID, EventType, EventName, ParentEventType, ParentEventName )
AS (
select /*TOP 1*/
child.EventID
, child.EventType
, child.EventName
, parent.eventtype
, parent.eventname
, 1 as TheLevel
from vaapps.tevent child
inner join vaapps.tevent parent
on child.parenteventid = parent.eventid
where parent.parenteventid is NULL
union all
select /*TOP 1*/
child.EventID
, child.EventType
, child.EventName
, parent.EventType
, parent.EventName
, parent.TheLevel + 1
from vaapps.tevent child
inner join cte parent
on child.parenteventid = parent.EventID
)
, cteLevels as ( select EventType
, EventName
, ParentEventType
, ParentEventName
, rank() over ( partition by TheLevel order by TheLevel, ParentEventType, EventType ) as RNK
from cte
)
select top ( 3 ) *
from cteLevels
where RNK = 1
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 12, 2012 at 11:48 pm
ALZDBA (6/11/2012)
Did you try using it with level indication ?
;
with cte ( EventID, EventType, EventName, ParentEventType, ParentEventName )
AS (
select /*TOP 1*/
child.EventID
, child.EventType
, child.EventName
, parent.eventtype
, parent.eventname
, 1 as TheLevel
from vaapps.tevent child
inner join vaapps.tevent parent
on child.parenteventid = parent.eventid
where parent.parenteventid is NULL
union all
select /*TOP 1*/
child.EventID
, child.EventType
, child.EventName
, parent.EventType
, parent.EventName
, parent.TheLevel + 1
from vaapps.tevent child
inner join cte parent
on child.parenteventid = parent.EventID
)
, cteLevels as ( select EventType
, EventName
, ParentEventType
, ParentEventName
, rank() over ( partition by TheLevel order by TheLevel, ParentEventType, EventType ) as RNK
from cte
)
select top ( 3 ) *
from cteLevels
where RNK = 1
Thank you ALZDBA, it looks this is what i want.
but, that this gives me duplicate rows in same 1st RNK, which i surprise.
any way, i got a clue to achieve by your way.
Thanks a lot again.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply