select distinct values of a replaced selected result

Question

Good day all.

I'm facing a little problem with a mySql query. let's assume we have a table with a coulmn in which the values are pairs, but unified in the same field, so something like this:

id  |  serviceName
----+-------------
1   |  foo - bar
2   |  foo - doo
3   |  foo - tep
4   |  bee - bar
5   |  bee - blo

I would like to select distinct the first part of serviceName, in this case foo, bee.

the desired output should be:

foo
bee

in the resultset.

what I've thought right now is something about making a SELECT DISTINCT a FROM REPLACE ( (SELECT serviceName as a FROM tableName), ' - ***', '')

but i'm not really sure if it is possible, and how to make it. I only would like to select the first part of the field, and I would like to take only distinct vlaues of it... it is possible? I need a right direction pointing,, I can make researches by my self.

thanks in advance.

show your desired output – M Khalid Junaid Apr 18 '14 at 15:07 — M Khalid Junaid, Apr 18 '14 at 15:07

score 1 · Accepted Answer · answered Apr 18 '14 at 15:08

1

Assuming that you always want to split on a dash -, this should work for you.

SELECT DISTINCT LEFT(serviceName, LOCATE('-', serviceName) - 2) FROM tableName;

SQLFiddle

answered Apr 18 '14 at 15:08

Patrick Q

6,373
2
25
34

this is also a really good way, is the distinct better than the other solution? – Matteo Bononi 'peorthyr' Apr 18 '14 at 15:14
1

@MatteoBononi'peorthyr' Performance-wise, there isn't much/any noticeable difference. Although I believe that if you get very specific, `DISTINCT` may be _slightly_ faster. That being said, if you want to do something like get the count of how many entries match each first section, then you'll want to use `GROUP BY`. – Patrick Q Apr 18 '14 at 15:17

score 1 · Answer 2 · answered Apr 18 '14 at 15:08

1

IS this what you are looking at ?

select substring_index(serviceName,'-',1) as `first_part` 
from test
group by `first_part`

DEMO

answered Apr 18 '14 at 15:08

Abhik Chakraborty

44,654
6
52
63

this is really aweesome, thanks, I'm only thinking about performance, is this better than a distinct? – Matteo Bononi 'peorthyr' Apr 18 '14 at 15:14
1

well both have some advantages and disadvantages you check here but for small data set it does not matter http://stackoverflow.com/questions/7943957/huge-performance-difference-when-using-group-by-vs-distinct – Abhik Chakraborty Apr 18 '14 at 15:16
thanks a lot, this is the thing i'm searching for, now i'm in difficult on which promote as the answer :/ – Matteo Bononi 'peorthyr' Apr 18 '14 at 15:17
1

You must promote the one you choose and the person who answered first and its Patrick Q – Abhik Chakraborty Apr 18 '14 at 15:19

select distinct values of a replaced selected result

2 Answers2