CODEWITHSUNDEEP
sql
satoru
satoru

Reputation: 33225

how to select distinct rows for a column

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.

Upvotes: 1

Views: 415

Answers (4)

Quassnoi
Quassnoi

Reputation: 425371

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.

Upvotes: 3

Senseful
Senseful

Reputation: 91681

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

Upvotes: 1

Tommi
Tommi

Reputation: 8608

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.)

Upvotes: 0

Salil
Salil

Reputation: 47482

Use just group by

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

Upvotes: 1

Related Questions