2

I am trying to make a query to get some results:

I have a table with some data:

client | price

1 | 100

1 | 150

1 | 200

2 | 90

2 | 130

2 | 200

3 | 95

3 | 120

3 | 250

I would like with one query to select the results and order it by price and client and get them in this form, ordered by the best price of each clint:

2 | 90

2 | 130

2 | 200

3 | 95

3 | 120

3 | 250

1 | 100

1 | 150

1 | 200
Improve this question
3
  • 1
    What should happen if two clients are ties on their lowest price, but not on their second lowest? Can they be returned in either order, or should the second lowest price be used as a tie-breaker? Commented Aug 1, 2010 at 16:28
  • Mark, second comment read, thank you for that remark. Corrected the answer. Commented Aug 1, 2010 at 16:29
  • @Alexander: The second comment is the first comment now :). I decided to delete my first comment as I am no longer interested in knowing the answer. If I know the answer to the second question that's enough for me. Commented Aug 1, 2010 at 16:33

1 Answer 1

Reset to default
3 
SELECT tbl.client, ytbl.price
FROM (SELECT client, min(price) as mpr FROM yourtable group by client) tbl
JOIN yourtable ytbl ON ytbl.client=tbl.client
ORDER BY tbl.mpr ASC, tbl.client ASC, ytbl.price ASC

Something like that...

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Hi Alexander, it works great! Thank you very much :-) Cheers Nik
Great to hear that! I see that you're new here. If you find the answer to your question, mark it as the correct answer. When you accept an answer you get points, so does the person who gives the correct answer. It's what keeps this forum going (besides altruism).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.