Select rows where column is different

Question

Surprisingly enough I couldn't find this question, so I come here asking for help.

I have a mysql table:

+---------+------------+---------+
|   id    |   link     |    col3 |
+---------+------------+---------+
|    1    |  site.com  |    a    |
+---------+------------+---------+
|    2    |  site.com  |    b    |
+---------+------------+---------+
|    3    |  test.com  |         |
+---------+------------+---------+

I'm trying select all the rows which have the link column different. for example rows with id 1 and 3 would return but not 2 because it's already in 1 (link).

and note: col3 always has a different value, and I sometimes link is empty.

I've tried doing:

SELECT DISTINCT link, id FROM table WHERE col3 = '$col_id' AND link IS NOT NULL

SELECT x.* FROM my_table JOIN (SELECT link, MIN(id) min_id FROM my_table GROUP BY link) y ON y.link = x.link AND y.min_id = x.id; I'm surprised you couldn't find it too. It IS asked AND answered EVERY SINGLE DAY in SO — Strawberry, May 30 '14 at 15:16
possible duplicate of [mySQL select one column DISTINCT, with corresponding other columns](http://stackoverflow.com/questions/5967130/mysql-select-one-column-distinct-with-corresponding-other-columns) — lurker, May 30 '14 at 15:17

spencer7593 · Answer 1 · 2014-05-30T15:46:03.690

I think you're looking for the resultset returned by this query:

SELECT t.link AS link
     , MIN(t.id) AS id
  FROM mytable t
 GROUP BY t.link

That will return "unique" values for the link, along with the id value from a row that has that link value.

If that doesn't return the resultset specified, then please specify the expected resultset, preferably by giving an example. (The return of link and id columns in the query in my answer are based on the expressions listed in OP query.

To return the entire row, you could use the resultset returned by the query above as an inline view, to retrieve the id values. Assuming id is a unique (or primary) key in mytable, for example:

SELECT r.id
     , r.link
     , r.col3
  FROM mytable r
  JOIN ( SELECT t.link AS link
              , MIN(t.id) AS id
           FROM mytable t
          GROUP BY t.link
       ) s
    ON s.id = r.id

Select rows where column is different

1 Answers1