[Solved] Custom row number in SQL

by


If you have a column in that table, that defines an order, you could get the section numbers with subqueries fetching the count of rows with lower ordered rows with the respective level.

Assuming the column defining the order is id something looking like this could be what you want.

SELECT col1,
       convert(varchar(max), (SELECT count(*)
                                     FROM myTable chart1
                                     WHERE chart1.id <= chart.id
                                           AND chart1.chart_level <= 2))
       +
       CASE
          WHEN (SELECT count(*)
                       FROM myTable chart2
                            WHERE chart2.id <= chart.id
                                  AND chart2.chart_level > 2
                                  AND (SELECT count(*)
                                              FROM myTable chart3
                                              WHERE chart3.id <= chart.id
                                                    AND chart3.chart_level <= 2)
                                       = (SELECT count(*)
                                                 FROM myTable chart4
                                                 WHERE chart4.id <= chart2.id
                                                       AND chart4.chart_level <= 2)) = 0
            THEN ''
          ELSE
            '.' + convert(varchar(max), (SELECT count(*)
                                                FROM myTable chart2
                                                WHERE chart2.id <= chart.id
                                                      AND chart2.chart_level > 2
                                                      AND (SELECT count(*)
                                                                  FROM myTable chart3
                                                                  WHERE chart3.id <= chart.id
                                                                        AND chart3.chart_level <= 2)
                                                           = (SELECT count(*)
                                                                     FROM myTable chart4
                                                                     WHERE chart4.id <= chart2.id
                                                                           AND chart4.chart_level <= 2)))
       END RowNo
       FROM myTable chart;

0

solved Custom row number in SQL