2

I need help with a relatively simple query. For a table:

A | B | C 
----------
2   1   6
2   2   5
3   3   4
4   4   3
5   5   2
6   6   1

I need to have an output like so:

A | B | C 
----------
2   1   6
3   3   4
4   4   3
5   5   2
6   6   1

So that each value in A is distinct, but I also get the corresponding values in B and C. I know "select distinct(A) from table" but that only returns the values 2,3,4,5,6 and I need the values in columns B and C, too. Please help. I have a deadline fast approaching. This question is stupid and trivial, but one must walk before they can run. Thanks so much.

Improve this question
3
  • Do you have a preference which set of B and C values you return? Commented Nov 30, 2011 at 22:16
  • If you define distinct a, you shoud also define which b and c you need (eg. MIN, MAX, FIRST,...). Commented Nov 30, 2011 at 22:18
  • Is there another column in the table that is an ID (Primary) key in the table to use as a basis of which B and C elements could be properly captured? Commented Dec 1, 2011 at 4:43

2 Answers 2

Reset to default
1

Try this:

SELECT T1.A, T1.B, MIN(T1.C) AS C
FROM yourtable T1
JOIN (
    SELECT A, MIN(B) AS B
    FROM yourtable
    GROUP BY A
) T2
ON T1.A = T2.A AND T1.B = T2.B
GROUP BY T1.A, T1.B
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Nice. it seems obvious in hindsight, but oh well. Appreciated!
1 
SELECT DISTINCT(A), B, C
FROM table

Is there a specific logic behind which distinct A rows you want to select when considering columns B and C?

Improve this answer

1 Comment

This won't work. THe DISTINCT applies to the entire row regardless of the parentheses around A.

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.