Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
    
"have created a table that joins with itself to get info of every match and then grouping" -- can you post this query? –  Ben Lee Feb 19 '13 at 23:06
    
I removed group by and ran it using PHP over results SELECT * FROM user_items u1, user_items u2 WHERE u1.user_id = "current user's id" AND u2.user_id != "current user's id" AND u1.item_id = u2.item_id; –  dinwal Nov 26 '13 at 7:59

1 Answer 1

Without seeing the query I can't be too helpful, but on the surface level it sounds like a simple ORDER BY clause and a LIMIT.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.