27064

TSQL USE ROW_NUMBER IN CTE, SELECT ORDER BY NOT WORKING

Question:

I preapare string from row of nubmers. When I use the row_number function, the order by clause not working

DECLARE @text VARCHAR(MAX) IF OBJECT_ID('tempdb..#numbers') IS NOT NULL DROP TABLE #numbers SELECT CAST(ROW_NUMBER() OVER (ORDER BY name) AS INT) AS number INTO #numbers FROM master..spt_values SET @text = '' ;WITH numbers (number) AS ( SELECT CAST(ROW_NUMBER() OVER (ORDER BY name) AS INT) AS number FROM master..spt_values ), a AS ( SELECT number FROM numbers WHERE number < 10 ), b AS ( SELECT number FROM numbers WHERE number < 10 ) SELECT @text = @text + LTRIM(STR(a.number*b.number)) FROM a CROSS JOIN b ORDER BY a.number, b.number DESC SELECT @text result "9" SET @text = '' ;WITH numbers (number) AS ( SELECT number FROM #numbers ), a AS ( SELECT number FROM numbers WHERE number < 10 ), b AS ( SELECT number FROM numbers WHERE number < 10 ) SELECT @text = @text + LTRIM(STR(a.number*b.number)) FROM a CROSS JOIN b ORDER BY a.number, b.number DESC SELECT @text

result "9876543211816141210864227242118151296336322824201612844540353025201510554484236302418126635649423528211477264564840322416881726354453627189"

Where is diference ?

Answer1:

I expect this is related to <a href="https://support.microsoft.com/en-us/kb/287515" rel="nofollow">this issue</a>, in summary when you use variable concatenation, e.g.

SELECT @Variable = @Variable + someField FROM Table ORDER BY AnotherField;

The results are dependant on physical implementation and internal access paths. I am currently struggling to find benchmark tests on the internet, but I think the fastest, reliable approach in SQL Server is to use XML extensions to concatenate rows to columns:

WITH Numbers AS (SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)) t (Number)) SELECT [Text] = (SELECT LTRIM(STR(a.number*b.number)) FROM Numbers AS A CROSS JOIN Numbers AS B ORDER BY A.Number, b.Number DESC FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)');

<em>N.B. I have also removed the reference to master..spt_values and replaced with a table value constructor - this just adds unnecessary reads to generate a sequence from 1 to 9.</em>

If you need more numbers for your sequence I would still not use system tables, use Iztik Ben-Gan's stacked CTE approach, as described in <a href="http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1" rel="nofollow">this article</a>:

DECLARE @Numbers INT = 100000; WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)), N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2), N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2), N4 (N) AS (SELECT 1 FROM N3 AS N1 CROSS JOIN N3 AS N2), Numbers (Number) AS (SELECT TOP (@Numbers) ROW_NUMBER() OVER(ORDER BY N) FROM N4) SELECT Number FROM Numbers;

Answer2:

Do not use cast on ROW_NUMBER(). This will return same as your second query:

DECLARE @text VARCHAR(MAX) = '' ;WITH numbers (number) AS ( SELECT ROW_NUMBER() OVER (ORDER BY name) AS number FROM master..spt_values ), a AS ( SELECT number FROM numbers WHERE number < 10 ), b AS ( SELECT number FROM numbers WHERE number < 10 ) SELECT @text = @text + LTRIM(STR(a.number*b.number)) FROM a CROSS JOIN b ORDER BY a.number, b.number DESC

Also don't define twice the same in CTE use aliases instead:

DECLARE @text VARCHAR(MAX) = '' ;WITH numbers (number) AS ( SELECT ROW_NUMBER() OVER (ORDER BY name) AS number FROM master..spt_values ), a AS ( SELECT number FROM numbers WHERE number < 10 ) SELECT @text = @text + LTRIM(STR(a.number*b.number)) FROM a AS a CROSS JOIN a AS b ORDER BY a.number, b.number DESC SELECT @text

Recommend

  • Sorting ngTable doesn't work when heading gets clicked
  • SQL Server 2005 - Select top N plus “Other”
  • Tensorflow for image segmentation: Changing minibatch size stops learning
  • Glassfish 5 will not start after enable-secure-admin command
  • Using XSL to get all tags before a tag with given contents occurs
  • Merging rows to columns
  • Why cepheus don't send int without quotes to orion?
  • Scala multiline string placeholder
  • Mysql - How to search for 26 records that each begins with the letter of the alphabet?
  • pyodbc doesn't report sql server error
  • Conversion from string “a” to type 'Boolean' is not valid
  • Array with custom indexes in Ionic2
  • Eloquent paginate function in Slim 3 project using twig
  • How can I enlarge video fullscreen without the affected interface project in as3?
  • Grails calculated field in SQL
  • Sort List of Strings By Version
  • how to display data from 1st point on words on y axis for line chart in d3.js
  • Set the selected item in dropdownlist in MVC3
  • jQuery .attr() and value
  • Highlight one bar in a series in highcharts?
  • Repeat a vertical line on every page in Report Builder / SSRS
  • Change JButton Shape while respecting Look And Feel
  • Getting last autonumber in access
  • How to check if every primary key value is being referenced as foreign key in another table
  • MySQL WHERE-condition in procedure ignored
  • How to handle AllServersUnavailable Exception
  • Display Images one by one with next and previous functionality
  • ORA-29908: missing primary invocation for ancillary operator
  • jquery mobile loadPage not working
  • How to get next/previous record number?
  • How do you join a server to an Active Directory (domain)?
  • how does django model after text[] in postgresql [duplicate]
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • Cant find why the layout is getting smaller
  • How does Linux kernel interrupt the application?
  • Bitwise OR returns boolean when one of operands is nil
  • MATLAB: Piecewise function in curve fitting toolbox using fittype
  • Observable and ngFor in Angular 2
  • UserPrincipal.Current returns apppool on IIS
  • java string with new operator and a literal