How to select partially distinct lines?

Asked

Viewed 114 times

5

I have a table whose column reference contains constructed values as follows::

Reference / Color / Size / Gender

Examples:

JOHN/WHITE/52/MALE
JOHN/WHITE/51/FEMALE
JOHN/BLACK/52/MALE
JOHN/BLACK/51/FEMALE
JANE/BLACK/XL/MALE
JANE/PINK/L/FEMALE
578/WHITE/L/FEMALE
578/BLACK/L/FEMALE

I tried using the REPLACE (English), but the first portion of the reference is not fixed in size.


Question

How can I select the records by getting only one for each reference found?

Example of the result to be obtained based on the examples above:

JOHN/WHITE/52/MALE
JANE/BLACK/XL/MALE
mysql query

  • You mean one by name? Whatever?

  • @bfavaretto Basically 1 for each initial portion of the reference, no matter which, but I edited the question to illustrate that the initial portion of the reference is of variable value.

1 answer

7


If either item will be selected for each name/reference, it is possible to achieve this result with SUBSTRING_INDEX:

SELECT * FROM refs
GROUP BY SUBSTRING_INDEX(reference, '/', 1)

http://sqlfiddle.com/#! 2/f1dec/1

The SUBSTRING_INDEX in this case returns a substring of the value of each reference until the first occurrence of /. The GROUP BY group the results by that value.

Browser other questions tagged mysql query

You are not signed in. Login or sign up in order to post.