Display duplicates with non duplicates

I have this table:

Name | add | city | id
开发者_开发技巧----------------------
dan  | df  | mum  |  1
abu  | kj  | del  |  2
abu  | kj  | del  |  3
abu  | bv  | kol  |  4
jas  | ol  | ch   |  5

The following query will display rows with the name abu:

Select * from table where name like'%abu%'

Now I want to eliminate duplicates. For that I can use the GROUP BY clause. But it doesn't display the rows which are not duplicate.

It should display like

abu kj del
abu bv kol

How can I achieve that?

if you want to eliminate duplicates just add DISTINCT like this:

Select DISTINCT * from table where name like'%abu%'

btw: making query with " LIKE '%something%' " is very unefficient. if you can in your scenario, it is better to make " LIKE 'abu%'

BTW: in your example, if you want to take records that have count(*) > 1 you can use HAVING clause:

 SELECT * from table GROUP BY column HAVING count(*) > 1

Something like this might give you what you are after:

SELECT DISTINCT
  Name,
  add,
  city
FROM atable
WHERE Name LIKE '%abu%'