MPetrovic MPetrovic - 8 days ago 7
SQL Question

How to display only unique row and to remove from appear column

Is it possible and how is possible to leave only unique values in column abc, and is it possible to not show column id1?
My query is

SELECT
br.id,LEFT( br.abc,3),
d.id
FROM
test br
LEFT JOIN i_test2 d ON d.id LIKE concat(br.abc, '%')
WHERE
d.id NOT LIKE '%0' AND d.id NOT LIKE '%9'


Result looks like:

id abc id1
1 C18 C181
2 C18 C182
3 D50 D504
4 D50 D506
5 D10 D106
6 D10 D107


I tried to solve also with adding

GROUP BY br.abc
HAVING COUNT(*) > 1 (also i tried with HAVING COUNT(br.abc) but id didn't work)


At the end, result should looks like:

ID abc
1 C18
3 D50
6 D10


Purpose of this is, i want to display values from table test that can't be reference on value in table test2 that ends with 0 or 9 because it not exist. And in result i want to see only unique value.

Answer

You could do something like this;

SELECT
      MIN(br.id) id,
     LEFT(br.abc,3) abc
FROM
     test br
LEFT  JOIN i_test2 d ON d.id LIKE concat(br.abc, '%')
WHERE
     d.id NOT LIKE '%0' 
     AND d.id NOT LIKE '%9'
GROUP BY LEFT(br.abc,3)

It will give you the first appearance of your column 'abc' based upon the id of that row.

Comments