CODEWITHSUNDEEP
mysql
Veltar
Veltar

Reputation: 741

only select the row if the field value is unique

I sort the rows on date. If I want to select every row that has a unique value in the last column, can I do this with sql?

So I would like to select the first row, second one, third one not, fourth one I do want to select, and so on.

enter image description here

Upvotes: 0

Views: 424

Answers (3)

Bohemian
Bohemian

Reputation: 425328

This non-standard, mysql-only trick will select the first row encountered for each value of pk_artikel_bron.

select *
...
group by pk_artikel_bron

Like it or not, this query produces the output asked for.

Edited

I seem to be getting hammered here, so here's the disclaimer:

This only works for mysql 5+

Although the mysql specification says the row returned using this technique is not predictable (ie you could get any row as the "first" encountered), in fact in all cases I've ever seen, you'll get the first row as per the order selected, so to get a predictable row that works in practice (but may not work in future releases but probably will), select from an ordered result:

select * from (
    select *
    ... 
    order by pk_artikel_id) x
group by pk_artikel_bron

Upvotes: -1

Marcus Adams
Marcus Adams

Reputation: 53870

This is similar to Michael's answer, but does it with a self-join instead of a subquery. Try it out to see how it performs:

SELECT * from tbl t1
LEFT JOIN tbl t2
  ON t2.fk_artikel_bron = t1.fk_artikel_bron
  AND t2.pk_artikel_id < t1.pk_artikel_id
WHERE t2.pk_artikel_id IS NULL

If you have the right indexes, this type of join often out performs subqueries (since derived tables don't use indexes).

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270767

What you want are not unique rows, but rather one per group. This can be done by taking the MIN(pk_artikel_Id) and GROUP BY fk_artikel_bron. This method uses an IN subquery to get the first pk_artikel_id and its associated fk_artikel_bron for each unique fk_artikel_bron and then uses that to get the remaining columns in the outer query.

SELECT * FROM tbl 
WHERE pk_artikel_id IN
  (SELECT MIN(pk_artikel_id) AS id FROM tbl GROUP BY fk_artikel_bron)

Although MySQL would permit you to add the rest of the columns in the SELECT list initially, avoiding the IN subquery, that isn't really portable to other RDBMS systems. This method is a little more generic.

It can also be done with a JOIN against the subquery, which may or may not be faster. Hard to say without benchmarking it.

SELECT * 
FROM  tbl
  JOIN (
    SELECT 
      fk_artikel_bron, 
      MIN(pk_artikel_id) AS id
    FROM tbl
    GROUP BY fk_artikel_bron) mins ON tbl.pk_artikel_id = mins.id

Upvotes: 3

Related Questions