SQL Server - Using ROW_NUMBER() OVER PARTITION function to SET a value

Question

Hi everyone thanks for taking some time to look into my question,

Background

I'm using the ROW_NUMER() function along with a PARTITION BY..ORDER BY statement to set a varchar value with an incrementing int value at the end of it for each of a value in one of my tables.

SELECT  component_module_id as component_module_id,
        component_property_id,
        'cf' + CAST(ROW_NUMBER() OVER (PARTITION BY component_module_id ORDER BY component_module_id) as VARCHAR) AS cf
FROM component_property

Here the 'cf' value goes from cf1-cfX for each component_module_id

My Question

Whenever I attempt to use these cf values elsewhere, like saved in a temp table, other Ordering and Grouping statements change these values. It's like the statements to generate the 'cf' values are saved and not the 'cf' values themselves.

After inserting the query above into a temp table #t -

SELECT * FROM #t ORDER BY cf

I receive 'cf' values that start at cf1 and jump to cf10 and then cf100, with a range of cf values from cf1 to cf900... I should only be receiving values ranging from c1 to cf29.

My question here is - Why are the values in this column treated differently than any other normal value? Why is the ROW_NUMBER() OVER (PARTITION BY....)) calculation being passed to further queries down the line? (if that's actually what's happening). And finally, how I can treat these 'cf' values like normal VARCHAR values and not have them change on me whenever I try to group or order by them.

Thanks for any help!

Update

I took the suggestion from Larnu,

"Seems like you'd be better off just storing the int value, and using a (PERSISTED) computed column to concatenate your prefix and ROW_NUMBER value."

and my 'cf' values are now appearing correctly after being sorted. Thanks everyone marking as solved.

Its normal that if you order by a varchar field 10 comes before 2, and 100 comes before 2 — zip, Feb 10 '20 at 16:38
you explicitly construct varchar values, when comparing chars, 1 < 10 < 2, but 01 < 02 < 10 — nfgl, Feb 10 '20 at 16:38
Seems like you'd be better off just storing the `int` value, and using a (`PERSISTED`) computed column to concatenate your prefix and `ROW_NUMBER` value. — Thom A, Feb 10 '20 at 16:40
I'm not confused about cf1 being sorted before cf10 and cf100. The actual values should be cf1-cf29. I should not be getting any values greater than cf29. My question is, how do I sort the correct values (cf1-cf29) without generating these incorrect values (cf100, cf200, etc). — wolly28, Feb 10 '20 at 17:33

SE1986 · Answer 1 · 2020-02-10T16:47:59.287

0

Try this

SELECT  component_module_id,
        component_property_id,
        'cf' + CAST(cf) AS VARCHAR(10)
FROM    (
            SELECT  component_module_id,
                    component_property_id,
                    ROW_NUMBER() OVER (PARTITION BY component_module_id ORDER BY component_module_id) AS cf
            FROM    component_property
        ) a
ORDER BY component_module_id

The behaviour you are experiencing is becauseVARCHAR orders numbers by doing an alphabetical sort

You need to do the ordering using an INT and then do your concatenation at the end (by wrapping the original query in a sub-query

However, a better solution was suggested in the comments which is to only store numbers as INT and add the prefix using a computed column (or at the query level)

edited Feb 10 '20 at 16:47

answered Feb 10 '20 at 16:42

SE1986

2,534
1
10
29

I'm not confused about cf1 being sorted before cf10 and cf100. The actual values should be cf1-cf29. I should not be getting any values greater than cf29. My question is, how do I sort the correct values (cf1-cf29) without generating these incorrect values (cf100, cf200, etc). – wolly28 Feb 10 '20 at 17:31
You need to edit your question so we can see the output for the "wrong" results – SE1986 Feb 10 '20 at 18:31

score 0 · Answer 2 · answered Feb 10 '20 at 16:43

0

If you do it this way, it will work

SELECT * FROM #t ORDER BY component_module_id, cast(replace(cf, 'cf', '') as int)

answered Feb 10 '20 at 16:43

zip

3,938
2
11
19

score 0 · Answer 3 · answered Feb 10 '20 at 17:58

The issue is that you are both partitioning by and ordering by component_module_id. Within each partition, the ordering keys have the same value.

Why does this matter? Sorting in SQL is NOT stable. That means that ties can be resolved either way. Run the query a second time, and you might get a different ordering (among the ties).

The simple solution is to change the ordering to be stable, and you have just the column to do that component_property_id. I would also recommend using CONCAT() so you don't need any type conversions:

SELECT component_module_id as component_module_id,
       component_property_id,
       CONCAT('cf',
              ROW_NUMBER() OVER (PARTITION BY component_module_id ORDER BY component_property_id)
             0 AS cf
FROM component_property

I should also note that your code uses VARCHAR with no length. This is a really bad idea in SQL Server. The default length varies by context and may not be big enough for certain values (although you don't have that problem in this case).

score 0 · Answer 4 · answered Feb 10 '20 at 20:54

Update

I took the suggestion from Larnu,

"Seems like you'd be better off just storing the int value, and using a (PERSISTED) computed column to concatenate your prefix and ROW_NUMBER value."

and my 'cf' values are now appearing correctly after being sorted. Thanks everyone marking as solved.

SQL Server - Using ROW_NUMBER() OVER PARTITION function to SET a value

4 Answers4