how to select distinct rows for a column

Question

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.

EDIT

I am using MySQL and my table contains more than the three columns shown here, so it seems to me that the inner query can not fulfill my requirement.

Answer 1

Use just group by

select id, name, min(observed_value) as minimum_val  from x group by name;

Answer 2

Your query looks to be too complex for your purpose... just group the query by column name, and use an aggregate function for rest of the columns. For example

SELECT name, min(id), min(observed_value) FROM x GROUP BY name

(Obviously, choose aggregate function other than min if you want to get other values for each name.)

Answer 3

Since you didn't specify which DBMS you are using, I'll provide a couple of solutions:

If you are using a DBMS that has the FIRST() aggregate function, you could use:

SELECT 
  FIRST(id) as id, 
  name, 
  FIRST(observed_value) as observed_value 
FROM x
GROUP BY name;

If you are using MySQL, you could use ORDER BY in conjunction with LIMIT to get something similar to a FIRST() aggregate function.

SELECT
  ( SELECT x2.id 
    FROM x as x2 
    WHERE x2.name = x.name 
    ORDER BY observed_value ASC 
    LIMIT 1
  ) AS id,
  name,
  MIN(observed_value) as observed_value
FROM x
GROUP BY name

Answer 4

SELECT  t.*
FROM    (
        SELECT  DISTINCT name
        FROM    mytable
        ) q
JOIN    mytable t
ON      t.id =
        (
        SELECT  id
        FROM    mytable ti
        WHERE   ti.name = q.name
        ORDER BY
                ti.name, ti.observed_value, ti.id
        LIMIT 1
        )

Create an index on (name, observed_value, id) for this query to be efficient.