locked
How to force a data come top when fetching from two table RRS feed

  • Question

  • User264732274 posted

    below code is working fine. i want to show a specific data at top from table Emp1

    select ID,Name, 1 as OrderID from Emp1 
    UNION ALL
    (
    	select ID,Name, 0 as OrderID from Emp2
    ) ORDER BY OrderID DESC
    

    my question is how can i do the same with joining two table instead of using Union ALL clause. thanks

    Friday, October 28, 2016 8:54 AM

Answers

  • User-2057865890 posted

    Hi sudip_inn,

    DECLARE @Emp1 TABLE(
        	ID INT,
        	Name VARCHAR(20)
    )
    
    INSERT INTO @Emp1 (ID,Name) SELECT 1, 'A1'
    INSERT INTO @Emp1 (ID,Name) SELECT 2, 'A2'
    INSERT INTO @Emp1 (ID,Name) SELECT 3, 'A3'
    INSERT INTO @Emp1 (ID,Name) SELECT 4, 'A4'
    
    
    DECLARE @Emp2 TABLE(
        	ID INT,
        	Name VARCHAR(20)
    )
    
    INSERT INTO @Emp2 (ID,Name) SELECT 5, 'B1'
    INSERT INTO @Emp2 (ID,Name) SELECT 6, 'B2'
    INSERT INTO @Emp2 (ID,Name) SELECT 7, 'B3'
    INSERT INTO @Emp2 (ID,Name) SELECT 8, 'B4'
    
    
    
    SELECT
    ISNULL(t.AID,t1.BID) AS ID,
    ISNULL(t.AName,t1.BName) AS Name,
    ISNULL(t.OrderID,t1.OrderID) OrderID
    FROM(
    SELECT A.ID AS AID, A.Name AS AName, 1 As OrderID FROM @Emp1 AS A
    ) t
    FULL JOIN 
    (
      SELECT B.ID AS BID,B.Name AS BName, 0 AS OrderID FROM @Emp2 AS B
    ) t1 ON t.AID = t1.BID 
    ORDER BY OrderID
    

    Best Regards,

    Chris

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 31, 2016 8:36 AM

All replies

  • User-2057865890 posted

    Hi sudip_inn,

    DECLARE @Emp1 TABLE(
        	ID INT,
        	Name VARCHAR(20)
    )
    
    INSERT INTO @Emp1 (ID,Name) SELECT 1, 'A1'
    INSERT INTO @Emp1 (ID,Name) SELECT 2, 'A2'
    INSERT INTO @Emp1 (ID,Name) SELECT 3, 'A3'
    INSERT INTO @Emp1 (ID,Name) SELECT 4, 'A4'
    
    
    DECLARE @Emp2 TABLE(
        	ID INT,
        	Name VARCHAR(20)
    )
    
    INSERT INTO @Emp2 (ID,Name) SELECT 5, 'B1'
    INSERT INTO @Emp2 (ID,Name) SELECT 6, 'B2'
    INSERT INTO @Emp2 (ID,Name) SELECT 7, 'B3'
    INSERT INTO @Emp2 (ID,Name) SELECT 8, 'B4'
    
    
    
    SELECT
    ISNULL(t.AID,t1.BID) AS ID,
    ISNULL(t.AName,t1.BName) AS Name,
    ISNULL(t.OrderID,t1.OrderID) OrderID
    FROM(
    SELECT A.ID AS AID, A.Name AS AName, 1 As OrderID FROM @Emp1 AS A
    ) t
    FULL JOIN 
    (
      SELECT B.ID AS BID,B.Name AS BName, 0 AS OrderID FROM @Emp2 AS B
    ) t1 ON t.AID = t1.BID 
    ORDER BY OrderID
    

    Best Regards,

    Chris

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 31, 2016 8:36 AM
  • User264732274 posted

    from sql perspective my one code sample was small and easier. anyway thanks

    Monday, October 31, 2016 9:12 AM