Custom row number in SQL

Question

I am using a software which doesn't have a feature on custom row number. In order to resolve my issue, I have to tweak it on my SQL and create a custom column for the custom row number. Here is what I want to do as my custom row number along with my select statement:

li{
list-style-type: none;
}

<ul>
<li>1.</li>
<li>2.</li>
<li>2.1</li>
<li>2.2</li>
<li>2.3</li>
<li>3.</li>
<li>3.1</li>
<li>3.2</li>
</ul>

I am using Microsoft SQL Server. I tried to use variables but it doesn't work the way I wanted to be. Here is my sample faulty output:

SELECT chart.col1,CASE
       WHEN  chart.CHART_LEVEL <= 2 THEN @no += 1
       WHEN chart.CHART_LEVEL => 3 THEN CONCAT(@no,'.'@subNo += 1) 
       ELSE ''
END 
AS 'RowNo'
FROM myTable chart

I am really sorry for giving insufficient details.

What's the database? The solution is highly dependent on the specifics of the database. — The Impaler, May 31 '18 at 00:40
When you added the SQL tag, you were shown a large message that suggested you also add a tag for the specific DBMS you're using, as functionality and syntax varies between them. Still, you chose to ignore that suggestion and not do so, which means you've delayed your chances of getting an answer and potentially wasted the time of people who try to help you only to find out it doesn't work in your DBMS. Please [edit] your question and add that tag, and pay attention to suggestions you receive in the future. We don't make them just to take up space on screen. — Ken White, May 31 '18 at 00:42
Have you thought about using css counters to do the list structure instead? See example here: https://stackoverflow.com/questions/4098195/can-ordered-list-produce-result-that-looks-like-1-1-1-2-1-3-instead-of-just-1 — Nathaniel Flick, May 31 '18 at 02:01
Sorry, But I want to output it using SQL as part of the column. — Rennjay, May 31 '18 at 02:32
Thank you for editing the question. Next time please also include your tabel's structure as a `CREATE TABLE` statement and sample date as `INSERT INTO` statements. — sticky bit, May 31 '18 at 02:42

score 0 · Accepted Answer · answered May 31 '18 at 02:40

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;

Custom row number in SQL

1 Answers1