How to get First, Second, Third Max Prices from another Table?

[iamprajot]

How to get First, Second, Third Max Prices from another Table?

[ausrine · Accepted solution]

@iamprajot

I believe RANKX() would be suitable here!

https://msdn.microsoft.com/en-us/library/gg492185.aspx

[iamprajot]

I have data similar to table A for Product & Prices (each product have 4 or 5 Prices) like

Product       Price

Tea       25

Tea       30

Sugar       15

Sugar       55

Tea       20

and

table B for Product and other data

Tea       XYZ

Sugar       XYZ

Now what I want is something like below and I have researched a lot and couldn't find a proper solution to that.

Product       Price 1       Price 2       Price 3

Tea       30       25       20

Sugar       55       15       -