Complicated TSQL custom ranking task

Question

I have tried a whole lot of variety of ranking solutions with joins and all to match the needs I want.

Sadly, I cannot come up with correct query to get the desired output.

I am really looking for any help to get explanation that would help me in future with these sort of tasks.

I have the following CTE table contains data values set:

 type    model    price    code
 Shoes    1298    700,00    1
 Shoes    1298    950,00    6
 Shoes    1298    1050,00    4
 Shoes    1321    970,00    2
 Shoes    1750    1200,00    3
 Shoes    1752    1150,00    5
 Pants    1121    850,00    2
 Pants    1121    850,00    4
 Pants    1121    850,00    5
 Pants    1232    350,00    8
 Pants    1232    350,00    9
 Pants    1232    400,00    7
 Pants    1232    600,00    1
 Pants    1233    600,00    3
 Pants    1233    950,00    6
 Pants    1233    970,00    12
 Pants    1233    980,00    11
 Pants    1260    350,00    10
 Hats    1276    400,00    1
 Hats    1288    400,00    6
 Hats    1401    150,00    4
 Hats    1408    270,00    5
 Hats    1433    270,00    2
 Hats    1434    290,00    3

Unified records numbering of CTE have to be done in the following manner: first there are the first models of the tables (Shoes, Pants and Hats), then the last models, after that - the second models in the tables, the penultimate, etc. In the case of exhaustion of the models of a particular type, number only remaining models of other types.

Here is the desired output:

Id    type    model    price           code
 1    Shoes    1298    700.0000       1
 2    Pants    1232    600.0000       1
 3    Hats    1276    400.0000        1

 4    Shoes    1298    950.0000       6
 5    Pants    1233    970.0000       12
 6    Hats    1288    400.0000        6

 7    Shoes    1321    970.0000       2
 8    Pants    1121    850.0000       2
 9    Hats    1433    270.0000        2

 10    Shoes    1752    1150.0000     5
 11    Pants    1233    980.0000      11
 12    Hats    1408    270.0000       5

 13    Shoes    1750    1200.0000     3
 14    Pants    1233    600.0000      3
 15    Hats    1434    290.0000       3

 16    Shoes    1298    1050.0000     4 
 17    Pants    1260    350.0000      10
 18    Hats    1401    150.0000       4

 19    Pants    1121    850.0000      4
 20    Pants    1232    350.0000      9

 21    Pants    1121    850.0000      5
 22    Pants    1232    350.0000      8

 23    Pants    1233    950.0000      6
 24    Pants    1232    400.0000      7

I have updated the desired output (added code column) to better understand the sorting idea. It has to be done in interleaved manner with first coded numbers(i.e. lowest code) of types goes first then the last coded numbers(i.e. highest code) of types goes second, then first coded who's left goes first and then the last coded who's left and etc.

Why does the row (9, Hats, 1433, 270.0000) appear before (12, Hats, 1408, 270.0000) in the desired output? I get that it needs to be shoes, hats, pants, shoes, hats, pants etc but how are you ordering beyond that? What determines which is the first record for shoes, and which is the last? — GarethD, May 11 '15 at 12:49
Tip: It is helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. Differences in syntax and features often affect the answers. — HABO, May 11 '15 at 13:16
I use MS SQL-Server 2012, so I prefer for TSQL solution. Tag is saying sql-server. — Kirill Pashkov, May 12 '15 at 12:40

Gordon Linoff · Answer 1 · 2015-05-12T18:35:32.580

1

You want the value interleaved. Here is how you can do this:

with cte as ( . . . )
select row_number() over (order by seqnum, charindex(type, 'ShoesPantsHats')) as id,
       t.*
from (select cte.*,
             row_number() over (partition by type order by (select NULL)) as seqnum
      from cte
     ) t
order by seqnum,
         charindex(type, 'ShoesPantsHats');

Note the order by clause for row_number(). SQL tables are inherently unordered, and if you care about the ordering of the results within each type, then put the appropriate logic there.

edited May 12 '15 at 18:35

answered May 11 '15 at 12:10

Gordon Linoff

1,242,037
58
646
786

1

[`CHOOSE`](https://msdn.microsoft.com/en-GB/library/hh213019.aspx) doesn't work like this, you are using it more like the MySQL Function [`FIELD`](https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field), the first argument is the index to choose from the rest, e.g. `SELECT CHOOSE(2, 'Shoes', 'Pants', 'Hats')` will return `Pants`. As it stands you will get a conversion error, I think you need to use a case statement to get the additional ordering - `ORDER BY seqNum, CASE [type] WHEN 'Shoes' THEN 1 WHEN 'Pants' THEN 2 WHEN 'Hats' THEN 3 ELSE 4 END` – GarethD May 11 '15 at 12:41
This is not exactly what I am after. Sortings are pretty wierd thats true. The logic is to get first set of 3 types those sorted on code asc, then get set of 3 types with the last code(desc), then set of 3 types with the 2nd code asc, then set of 3 types with 2nd last code(desc) and etc. When table out of sets of 3 keep the sequence with the ones those left. – Kirill Pashkov May 12 '15 at 12:46

score 1 · Answer 2 · answered May 11 '15 at 13:08

I can not really understand the ordering behind the scene, because the result set is not ordered nor by model nor by code, but here is the idea and you can play with orderings in CTEs:

WITH    cte1 AS ( SELECT   * ,
                        ROW_NUMBER() OVER (PARTITION BY type ORDER BY model, code) rn1
                  FROM     @t),
        cte2 AS ( SELECT   * ,
                        ROW_NUMBER() OVER (PARTITION BY rn1 ORDER BY 
                                            CASE type WHEN 'Shoes' THEN 1 
                                                      WHEN 'Pants' THEN 2 
                                                      WHEN 'Hats' THEN 3 END) rn2
                  FROM     cte1 )
SELECT  * ,
        ROW_NUMBER() OVER (ORDER BY rn1, rn2) rn
FROM    cte2

Output:

type    model   price   code    rn1 rn2 rn
Shoes   1298    700.00  1       1   1   1
Pants   1121    850.00  2       1   2   2
Hats    1276    400.00  1       1   3   3
Shoes   1298    1050.00 4       2   1   4
Pants   1121    850.00  4       2   2   5
Hats    1288    400.00  6       2   3   6
Shoes   1298    950.00  6       3   1   7
Pants   1121    850.00  5       3   2   8
Hats    1401    150.00  4       3   3   9
Shoes   1321    970.00  2       4   1   10
Pants   1232    600.00  1       4   2   11
Hats    1408    270.00  5       4   3   12
Shoes   1750    1200.00 3       5   1   13
Pants   1232    400.00  7       5   2   14
Hats    1433    270.00  2       5   3   15
Shoes   1752    1150.00 5       6   1   16
Pants   1232    350.00  8       6   2   17
Hats    1434    290.00  3       6   3   18
Pants   1232    350.00  9       7   1   19
Pants   1233    600.00  3       8   1   20
Pants   1233    950.00  6       9   1   21
Pants   1233    980.00  11      10  1   22
Pants   1233    970.00  12      11  1   23
Pants   1260    350.00  10      12  1   24

Rn contains desired values.

score 0 · Answer 3 · answered May 11 '15 at 12:32

0

Try this,

with cte as (select type, model,price, ROW_NUMBER() over (partition by type order by type) as rowid from temp)
select * from cte order by rowid, type

answered May 11 '15 at 12:32

Amnesh Goel

2,617
3
28
47

Complicated TSQL custom ranking task

3 Answers3