Highest Lowest and, Subquery

All Replies

• Monday, February 27, 2012 10:41 PM

   

   
  

  0

  There is a simple issue within your data. 

  Client 108's manager is 103. Client 103's manager is 108. So, you are in infinite loop.

  But if you make that correction, you will get something like this:

  Query 1: 

   Declare @TestTable Table (ClientID Int, ClientFirstname Varchar(20), ClientLastName Varchar(20), ManagerID Int, Salary Money)
  Insert Into @TestTable 
  Select 101	 , 'Tirthak', 'Shah', 104   , 1000 Union All 
  Select 102	 , 'Viral',	'Shah', 105   , 2000 Union All 
  Select 103	 , 'Yash', 'Shah',  108   , 3000 Union All 
  Select 104	 , 'Hardik', 'Shamnani' , 106   , 4000 Union All 
  Select 105	 , 'Kishan', 'Sinojia' ,  101   , 5000 Union All 
  Select 106	 , 'Bhagyashree', 'Soni' , 107   , 6000 Union All 
  Select 107	 , 'Ashish', 'Shrivastav' , 102   , 4000 Union All 
  Select 108	 , 'Aarsh',	'Talati' , 103   , 2000
  
  
  ;With CTE 
  As
  (
  	Select 
  		ClientInfo.ClientID 
  		,ClientInfo.ClientFirstname + ' ' + ClientInfo.ClientLastName As ClientName 
  		,ClientInfo.ManagerID 
  		,ManagerInfo.ClientFirstname + ' ' + ManagerInfo.ClientLastName As ManagerName 
  	From 
  		@TestTable As ClientInfo 
  		Inner Join @TestTable As ManagerInfo On ClientInfo.ManagerID = ManagerInfo.ClientID
  	Union All 
  	Select 
  		CTE.ManagerID 
  		,CTE.ManagerName
  		,SubQry.ClientID
  		,SubQry.ClientFirstname + ' ' + SubQry.ClientLastName As ManagerName 
  	From 
  		CTE 
  		Inner Join @TestTable As SubQry On CTE.ManagerID= SubQry.ClientID
  )
  Select * From CTE Option (MaxRecursion 1000)

  
  

  Query 2:

  Declare @TestTable Table (ClientID Int, ClientFirstname Varchar(20), ClientLastName Varchar(20), ManagerID Int, Salary Money)
  Insert Into @TestTable 
  Select 101	 , 'Tirthak', 'Shah', 104   , 1000 Union All 
  Select 102	 , 'Viral',	'Shah', 105   , 2000 Union All 
  Select 103	 , 'Yash', 'Shah',  108   , 3000 Union All 
  Select 104	 , 'Hardik', 'Shamnani' , 106   , 4000 Union All 
  Select 105	 , 'Kishan', 'Sinojia' ,  101   , 5000 Union All 
  Select 106	 , 'Bhagyashree', 'Soni' , 107   , 6000 Union All 
  Select 107	 , 'Ashish', 'Shrivastav' , 102   , 4000 Union All 
  Select 108	 , 'Aarsh',	'Talati' , 103   , 2000
  
  ;With CTE 
  As
  (
  	Select * 
  		,ROW_NUMBER() Over(Order By Salary Desc) As TopSalaryDesc
  		,ROW_NUMBER() Over(Order By Salary Asc) As TopSalaryAsc
  	From @TestTable
  )
  
  Select * From CTE 
  Where TopSalaryAsc = 2 Or TopSalaryDesc = 2 
  
  --output
  ClientID	ClientFirstname	ClientLastName	ManagerID	Salary	TopSalaryDesc	TopSalaryAsc
  102	Viral	Shah	105	2000.00	6	2
  105	Kishan	Sinojia	101	5000.00	2	7

  
  

  ---

  Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

  • Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Tuesday, February 28, 2012 1:32 AM
  • Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Sunday, March 04, 2012 4:07 PM
  •  
  • Reply
  • Quote

   
• Monday, February 27, 2012 10:42 PM

   

   
  

  0

  Preparing for interview ?? JJ

  • Reply
  • Quote

   
• Monday, February 27, 2012 11:07 PM

   

   
  

  0

  not using CTE.

  declare @human table(clientID int, clientFirstName sysname, clientLastName sysname, managerID int, Salary int)

  insert into @human(clientID,clientFirstName,clientLastName,managerID,Salary)
   values  (101,'Tirthak','Shah',104,1000)
    ,(102,'Viral','Shah',105,2000)
    ,(103,'Yash','Shah ',108,3000)
    ,(104,'Hardik','Shamnani',106,4000)
    ,(105,'Kishan','Sinojia',101,5000)
    ,(106,'Bhagyashree','Soni',107,6000)
    ,(107,'Ashish','Shrivastav ',102,4000)
    ,(108,'Aarsh ','Talati',103,2000)

  
  select m.clientid, h.clientid
   , m.clientFirstName+'.'+m.clientLastName+' manages '+ h.clientFirstName+'.'+h.clientLastName
  From  
   (select hm.clientID, hm.clientFirstname, hm.clientLastName
    From (select distinct managerid from @human) mn
    join @human hm on (mn.managerid = hm.clientid)) m
  join @human h on (h.managerid = m.clientid)
  order by m.clientID, h.clientID

  SELECT TOP 1 salary
   FROM (
   SELECT DISTINCT TOP 2 salary
   FROM @human
   ORDER BY salary DESC) h
   ORDER BY salary
   
   SELECT TOP 1 salary
   FROM (
   SELECT DISTINCT TOP 2 salary
   FROM @human
   ORDER BY salary ) h
   ORDER BY salary DESC

  • Reply
  • Quote