I saw a concise TSQL statement that effectively splits a string into its constituent characters, one per line, for the purpose of evaluating the
ascii value on each character.
If I am reading the query correctly, effectively, 3 CTEs are being used to prepare a table of 1 column containing 10,000 rows, each with the value '0'.
A fourth CTE is defined as follows:
cteTally(n) AS( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n FROM E4 )
Subsequently, this CTE is joined to a table containing a column with the strings of interest, with the following
SELECT n, SUBSTRING(LastName, n, 1), ASCII( SUBSTRING(LastName, n, 1))
That is, row number n, then the nth character in LastName, then the ascii value of that character.
My questions relate to the
over clause in the CTE above.
Essentially, what exactly is it doing?
If we are querying row_number from 10,000 identical rows, why do we need an
order by clause at all? Why is the
order by put into an
over clause rather than as an
order by clause for the
select statement - especially as the
over clause isn't even specifying any partition? (I presume this means the window over which
row_number operates is the full 10,000 rows?) And what does it mean to order by