question

AgentK avatar image
AgentK asked

Complicated TSQL custom ranking task

Hello. 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 1 Shoes 1298 700.0000 2 Pants 1232 600.0000 3 Hats 1276 400.0000 4 Shoes 1298 950.0000 5 Pants 1233 970.0000 6 Hats 1288 400.0000 7 Shoes 1321 970.0000 8 Pants 1121 850.0000 9 Hats 1433 270.0000 10 Shoes 1752 1150.0000 11 Pants 1233 980.0000 12 Hats 1408 270.0000 13 Shoes 1750 1200.0000 14 Pants 1233 600.0000 15 Hats 1434 290.0000 16 Shoes 1298 1050.0000 17 Pants 1260 350.0000 18 Hats 1401 150.0000 19 Pants 1121 850.0000 20 Pants 1232 350.0000 21 Pants 1121 850.0000 22 Pants 1232 350.0000 23 Pants 1233 950.0000 24 Pants 1232 400.0000
querytsqlhelprankingquery-hint
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.