I have a table with the following data, where I need to calculate a sort index (integer) for each row in TSQL
| type | code |
|---|---|
| Fruit | 030 |
| Fruit | 040 |
| Fruit | Banana |
| Fruit | Apple 1 |
| Fruit | Apple 2 |
| Soda | 050 |
| Soda | 1 |
| Soda | 054 |
| Soda | Sprite |
| Soda | Fanta |
The sort_index column below should be calculated by type (starting from 1 for each type) and code where parsable integer codes always takes precedence over alphanumeric codes:
| type | code | sort_index |
|---|---|---|
| Fruit | 030 | 1 |
| Fruit | 040 | 2 |
| Fruit | Apple 1 | 3 |
| Fruit | Apple 2 | 4 |
| Fruit | Banana | 5 |
| Soda | 1 | 1 |
| Soda | 050 | 2 |
| Soda | 054 | 3 |
| Soda | Fanta | 4 |
| Soda | Sprite | 5 |
Any help would be highly appreciated.
CodePudding user response：
Managed to find the answer I searched for.
SELECT
[type],
[code],
row_number() OVER (PARTITION BY [type] ORDER BY CASE WHEN try_parse(code AS INT) IS NOT NULL THEN CAST(code AS INT) ELSE 2147483647 END, code) AS [sort_index]
FROM
dbo.test_table
CodePudding user response：
Try this
ROW_NUMBER() over (PARTITION BY code Order by type) as sort_index