ProductCode | MinimumLevel | Bonus |
1 | 5 | 2 |
1 | 10 | 3 |
1 | 20 | 5 |
2 | 5 | 2 |
3 | 8 | 2 |
3 | 12 | 3 |
SQL Query
select p.ProductCode,
p.MinimumLevel,
(select
top 1 q.MinimumLevel from ProductTable q where q.MinimumLevel > p.MinimumLevel and q.ProductCode = p.ProductCode)
as MaximumLevel,
p.Bonus
from ProductTable p
ProductCode | MinimumLevel | MaximumLevel | Bonus |
1 | 5 | 10 | 2 |
1 | 10 | 20 | 3 |
1 | 20 | NULL | 5 |
2 | 5 | NULL | 2 |
3 | 8 | 12 | 2 |
3 | 12 | NULL | 3 |
1 comment:
Saying "Inner query" would be more suitable than "Inner loop". This is a bad practice with larger tables.
Post a Comment