CODEWITHSUNDEEP
sqloracle-databasesortingssmshierarchical
puk82
puk82

Reputation: 11

SQL Server Hierarchical Sorting without IDs

Given an unsorted table:

Column1 Column2 Rank
elmo Points -2
grouch cough 3
Points Points 2
elmo sneeze -4
grouch Points 4
elmo yell 2
grouch bite 1

I need to sort it based on:

  1. 'Points' values are prioritized the highest for each Column1 category, regardless of Rank
  2. The whole table, and also each category, is sorted by Rank descending

So the final table will have Points / Points as the first row since that value is prioritized, therefore showing the overall Rank, and then Points values will be at the top of every Colunmn1 category, with Rank descending in value, like this:

Column1 Column2 Rank
$total Points 2
grouch Points 4
grouch cough 3
grouch bite 1
elmo Points -2
elmo yell 2
elmo sneeze -4

Ideally, the code should work with tables with n columns (potentially more than just 2).

I'm trying to use a recursive CTE with a self-join to append a column for each iteration, but I'm very new to the concept and still an amateur with my SQL in general (in SSMS):

WITH cte AS
(
SELECT   column1 --Anchor
,column2
,rank
FROM MyTable
WHERE property0 = 'Points' --Since points in Column1 would be the highest level
UNION ALL
SELECT   t.column1 --Recursive
,t.column2
,t.points
FROM MyTable t
INNER JOIN cte
ON t.column2 = cte.column2 AND t.points = cte.points 
)
SELECT * FROM cte
ORDER BY points DESC

But this is just giving me an maximum recursion limit error.

Upvotes: 1

Views: 77

Answers (1)

MT0
MT0

Reputation: 168470

You can use ORDER BY and a CASE expression to set the priority of the rows:

SELECT Property0,
       Property1,
       net_sales
FROM   (
  SELECT Property0,
         Property1,
         net_sales,
         SUM(net_sales) OVER (PARTITION BY Property0) AS total_net_sales
  FROM   table_name
) t
ORDER BY
       CASE Property0 WHEN '$total' THEN 0 ELSE 1 END,
       total_net_sales DESC,
       Property0,
       CASE Property1 WHEN '$total' THEN 0 ELSE 1 END,
       net_sales DESC,
       Property1

or you can use ROLLUP (if you ignore the existing $total rows); which will generate rows for the totals with NULL values and then you can can use COALESCE to replace the NULLs with '$total'.

SELECT Property0,
       Property1,
       net_sales
FROM   (
  SELECT COALESCE(Property0, '$total') AS Property0,
         COALESCE(Property1, '$total') AS Property1,
         SUM(net_sales) AS net_sales,
         SUM(SUM(net_sales)) OVER (PARTITION BY Property0) AS total_net_sales,
         GROUPING_ID(Property0) AS gid0,
         GROUPING_ID(Property1) AS gid1
  FROM   table_name t
  WHERE  Property0 != '$total'
  AND    Property1 != '$total'
  GROUP BY ROLLUP(Property0, Property1)
) t
ORDER BY 
       gid0 DESC,
       total_net_sales DESC,
       Property0,
       gid1 DESC,
       net_sales DESC,
       Property1

Which, for the sample data:

CREATE TABLE table_name (
  Property0 VARCHAR(10),
  Property1 VARCHAR(10),
  net_sales INT
);

INSERT INTO table_name (Property0, Property1, net_sales) VALUES
  ('bar',    '$total', -200),
  ('foo',    'sauce',   300),
  ('$total', '$total',  200),
  ('bar',    'sup',    -400),
  ('foo',    '$total',  400),
  ('bar',    'bro',     200),
  ('foo',    'bacon',   100);

Both output:

PROPERTY0 PROPERTY1 NET_SALES
$total $total 200
foo $total 400
foo sauce 300
foo bacon 100
bar $total -200
bar bro 200
bar sup -400

Oracle fiddle SQL Server fiddle

Upvotes: 3

Related Questions