How to Keep a specific row as first result for a given group

• Naidu PK

  SSCrazy

  Points: 2481

  More actions

  September 17, 2018 at 3:43 pm

  #368327

  Hi,

  I have two datasets. When I merge this data, I want data to be sorted by CustId but the row with Name=Book Income should be first row within that custid group. 
  I am able to achieve this using a sub query. But, is there a way to achieve similar output without subquery. Real data is in the order of 400k rows so thinking if there is a way to avoid this sub query just for sorting data.

  --Dataset 1
CREATE TABLE #BkInc
(
  CustId TINYINT NOT NULL,
  Name VARCHAR(20) NOT NULL,
  Amount MONEY NOT NULL
);

  INSERT #BkInc
(
  CustId,
  Name,
  Amount
)
VALUES
(10, 'Book Income', 1000),
(20, 'Book Income', 2000);

  --Dataset 2
CREATE TABLE #Details
(
  CustId TINYINT NOT NULL,
  Name VARCHAR(20) NOT NULL,
  Amount MONEY NOT NULL
);
INSERT #Details
(
  CustId,
  Name,
  Amount
)
VALUES
(10, 'Bonus', 100),
(10, 'Expense', 200),
(20, 'Bonus', 100),
(20, 'Expense', 200);

  --Final output with subquery
SELECT CustId,
   Name,
   Amount
FROM
(
  SELECT CustId,
    Name,
    Amount
  FROM #BkInc AS BI
  UNION ALL
  SELECT CustId,
    Name,
    Amount
  FROM #Details AS D
) A
ORDER BY A.CustId,
   CASE
     WHEN Name = 'Book Income' THEN
      0
     ELSE
      1
   END;


  Thanks,
  Naveen.
  Every thought is a cause and every condition an effect

• pietlinden

  SSC Guru

  Points: 63506

  More actions

  September 17, 2018 at 3:56 pm

  #2006064

  Why not just add a sort of phony column for each of the two sets. For the book income, set the value = 1 and for the other set the value = 2.
  Then you can just do a normal sort. No fancy anything required.

  --Final output with subquery
  SELECT CustId,
 Name,
 Amount
FROM
(
SELECT CustId,
  Name,
  Amount,
     1 As SortSeq
FROM #BkInc AS BI
UNION ALL
SELECT CustId,
  Name,
  Amount,
    2 AS SortSeq
FROM #Details AS D
) A
ORDER BY A.CustId,
SorSeq
 END;

• souLTower

  SSCarpal Tunnel

  Points: 4026

  More actions

  September 18, 2018 at 7:57 am

  #2006121

  I think this will work but I don't know that it really saves you much.  The subquery that you used doesn't go back to the same table again, it just organizes your original query.  However this approach might make it easier if you needed to included limiting WHERE clauses in the union.

  
SELECT CustId, Name, Amount, CASE WHEN Name='Book Income' THEN 0 ELSE 1 END AS SRT
FROM #BkInc AS BI
UNION ALL
SELECT CustId, Name, Amount, 1 AS SRT
FROM #Details AS D
ORDER BY CUSTID, SRT