I have an interesting problem for you all. I have a partial solution but I feel you guys can come up with an efficient solution.
What I have a SQL table with following structure:
StockId <- a unique ID for the share
StockHolderId <- a unique ID for the share holder
StockPrice <- The price of the stock - if this is a request to purchase this is kept -1
BuyingorSelling <- value of 1 means the holder wants to buy, value 2 means he is selling
StockQuality <- a special record that stores a value calculated based on some complex computation. The values are 1, 2, or 3 based on let us say bad, neutral, good.
What I want to do is to find out who all can give me the stock I want to buy and who can purchase the ones I got. I want the person who gives a good quality stock (local value) at cheapest price (global value) to be my best selling match and the person who can buy most (semi global) of my stocks to be my best buying match.
The formula to rank the seller is 0.5 * price + 0.3 * availability + 0.2 * quality.
How would you go about it? Any ideas? I have created a table that joins with itself to get info of every match and then grouping but how can I acquire the price value to be cheapest?