how to select distinct rows for a column
- by Satoru.Logic
Hi, all.
I have a table x that's like the one bellow:
id | name | observed_value |
1 | a | 100 |
2 | b | 200 |
3 | b | 300 |
4 | a | 150 |
5 | c | 300 |
I want to make a query so that in the result set I have exactly one record for one name:
(1, a, 100)
(2, b, 200)
(5, c, 300)
If there are multiple records corresponding to a name, say 'a' in the table above, I just pick up one of them.
In my current implementation, I make a query like this:
select x.* from x ,
(select distinct name, min(observed_value) as minimum_val
from x group by name) x1
where x.name = x1.name and x.observed_value = x1.observed_value;
But I think there may be some better way around, please tell me if you know, thanks in advance.
