CODEWITHSUNDEEP
mysql
Milo Gertjejansen
Milo Gertjejansen

Reputation: 511

MySQL SELECT DISTINCT to include duplicates

I have a query, such as the one below.

SELECT DISTINCT(`name`) FROM `mydb`
WHERE `name` IN ('a', 'b', 'b', 'c', 'd', 'd', 'e');

I am providing the list of letters above and querying my database.

What I want is for it to return rows including the duplicates (so rows that have a name of 'a', two 'b' rows, 'c', two 'd' rows, and 'e').

Thanks in advance!

EDIT:

CURRENT Output:

+------+
| name |
+------+
| a    |
| b    |
| c    |
| d    |
| e    |
+------+

DESIRED Output:

+------+
| name |
+------+
| a    |
| b    |
| b    |
| c    |
| d    |
| d    |
| e    |
+------+

I cannot remove distinct. I should have provided more background. Each entry in the table has the potential to have another row with a duplicate name in it.

The reason why I need distinct is because I don't want 30 rows for the name 'a'. I just want one row for each entry that I input into the query.

Upvotes: 1

Views: 555

Answers (4)

7alhashmi
7alhashmi

Reputation: 924

You can do it using union all like this:

select distinct name from t1 where name in ('a','c','e')
Union all
select distinct name from t1 where name in ('a')
order by name;

SQL Fiddle

EDITED

Put all the distinct rows togather in one single condition and add any other duplicate row you need.

Upvotes: 1

Einar Sundgren
Einar Sundgren

Reputation: 4423

The DISTINCT keyword tells mysql to not show any duplicate rows. Just use your query without distinct.

Upvotes: 0

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

SELECT `name` FROM `mydb`
WHERE `name` IN ('a', 'b', 'b', 'c', 'd', 'd', 'e');

Upvotes: 0

Ed Heal
Ed Heal

Reputation: 59987

Is the word DISTINCT a give away. Just remove it!

Upvotes: 1

Related Questions