none
Highest Lowest and, Subquery

    Question

  • I have a table named: TestTable.

    The schema is as below :

    -------------------------------------------------------------------------------------

    | ClientId | ClientFirstName | ClientLastName | ManagerId | Salary |

    -------------------------------------------------------------------------------------

    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
     

    --------------------------------------------------------------------------------------


    I want to perform two operations:


    1.

    I want to simply print statements like:

    <Aarsh> is manager of <Yash>

    <Viral> is manager of <Ashish>

    ...

    .

    .

    .

    2.

    I want to display the second highest and second lower-highest salaries from the table.


    • Edited by Aarsh (MCTS) Monday, February 27, 2012 10:26 PM
    • Changed type Naomi NModerator Tuesday, February 28, 2012 1:31 AM Question rather than discussion
    Monday, February 27, 2012 10:23 PM

Answers

  • 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.

    Monday, February 27, 2012 10:41 PM

All replies

  • 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.

    Monday, February 27, 2012 10:41 PM
  • Preparing for interview ?? JJ

    Monday, February 27, 2012 10:42 PM
  • 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

    Monday, February 27, 2012 11:07 PM