Link to home
Start Free TrialLog in
Avatar of altariamx2003
altariamx2003Flag for Mexico

asked on

select the first row from similar matches

I have the table solicitudes

-------------------------------------------------------------------
|         enterprise                               |      state      |
|----------------------------------------------|----------------|
|     Saint Gobain Sekurit                  |   morelos    |
|     Saint Gobain Sekurit México     |   morelos    |
|     mabe                                          |   guerrero    |
|     mabe                                          |   guerrero    |
|     mabe México                             |   morelos     |
-------------------------------------------------------------------

I would like to do a query listing by  enterprise  with a condition by state, right now I use this query:

SELECT enterprise WHERE state = 'morelos' GROUP BY enterprise

Open in new window


and it give this result
-------------------------------------------------------------------
|         enterprise                               |      state      |
|----------------------------------------------|----------------|
|     Saint Gobain Sekurit                  |   morelos    |
|     Saint Gobain Sekurit México     |   morelos    |
|     mabe México                             |   morelos     |
-------------------------------------------------------------------

But What can I do to group enterprise names and just to list the first one when I found similar names of enterprises, for example
-------------------------------------------------------------------
|         enterprise                               |      state      |
|----------------------------------------------|----------------|
|     Saint Gobain Sekurit                  |   morelos    |
|     mabe México                             |   morelos     |
-------------------------------------------------------------------


Best regards
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

What constitutes a similar name - in this case it might require human knowledge rather than a rule.

I would do the following

SELECT DISTINCT enterprise FROM solicitudes;

Put those in a table - then in a second column put the grouping value you want so for insance
---------------------------------------------------------------------
|         enterprise                  |      state                  |
|-------------------------------------|-----------------------------|
|    Saint Gobain Sekurit México      |   Saint Gobain Sekurit      |
|    Saint Gobain Sekurit             |   Saint Gobain Sekurit      |
---------------------------------------------------------------------

Open in new window

You can then do a grouping on the join of this table and your solicitudes table
Avatar of altariamx2003

ASKER

Hi Julian I try this

SELECT DISTINCT T.enterprise
FROM solicitudes T
   LEFT JOIN solicitudes T2 ON T.enterprise != T2.enterprise
WHERE T.enterprise LIKE CONCAT('%', T2.enterprise, '%') AND T.state = 'Morelos'
UNION 
SELECT DISTINCT T2.enterprise
FROM solicitudes T
   LEFT JOIN solicitudes T2 ON T.enterprise != T2.enterprise
WHERE T.enterprise LIKE CONCAT('%', T2.enterprise, '%') AND T.state = 'Morelos'
ORDER BY enterprise

Open in new window


And it give me the same result as I try before
-------------------------------------------------------------------
|         enterprise                               |      state      |
|----------------------------------------------|----------------|
|     Saint Gobain Sekurit                  |   morelos    |
|     Saint Gobain Sekurit México     |   morelos    |
|     mabe México                             |   morelos     |
-------------------------------------------------------------------

This table is filled in a form that I use a subscription of a public bulletin, and as you can see I receive a lot of data with diferent format, for example:
Saint Gobain Sekurit    
Saint Gobain Sekurit México
SAIN GOBAIN MEXICO
saint gobain
etc............

And In the private system when the guys from marketing try to do a list of the name of the enterprises in the state of 'Morelos' they have that result.

Thats way Im looking for how to show the first one when I found similar names of enterprises, like this
-------------------------------------------------------------------
|         enterprise                               |      state      |
|----------------------------------------------|----------------|
|     Saint Gobain Sekurit                  |   morelos    |
|     mabe México                             |   morelos     |
-------------------------------------------------------------------
ASKER CERTIFIED SOLUTION
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
aahh

I see

I think I understand your suggestion, let me try it, perhaps it works for me

thanks
You are welcome feel free to post back if you have questions.