How to select partially distinct lines?

Navigation
5

I have a table whose column reference contains values constructed 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 SUBSTR (English) , but the first portion of the reference is not a fixed length.


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
    
asked by anonymous 06.01.2014 / 21:35

1 answer

7

If either item is selected for each name / reference, you can achieve this result with SUBSTRING_INDEX : 

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

link

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

    
06.01.2014 / 21:51
Execution of multiple functions within loops * pply How do I validate an e-mail field in Dart?