About Me

My photo
a Dynamic and Energetic guy.....

Wednesday, February 24, 2010

SQL Inner Query to get data without a cursor

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:

Sandun said...

Saying "Inner query" would be more suitable than "Inner loop". This is a bad practice with larger tables.

My Masters