altariamx2003
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:
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
--------------------------
| 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
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
ASKER
Hi Julian I try this
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 |
-------------------------- ---------- ---------- ---------- ---------- -
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
aahh
I see
I think I understand your suggestion, let me try it, perhaps it works for me
thanks
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.
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
Open in new window
You can then do a grouping on the join of this table and your solicitudes table