Question 21950 in Mysql Question posted in
The official MySQL documentation can be found here.

Select first distinct rows on two columns – Mysql

I have a table similar to below (PatientData)

| id_number | phone        | email            | dob       |
| --        | ----------   | ----             | ----      |
| 6         | 04-------61  | [email protected]| 196949200 |
| 5         | 04-------61  | [email protected]| 296949200 |
| 4         | 55-------51  | [email protected]| 396949200 |
| 3         | 04-------61  | [email protected]   | 496949200 |
| 2         | 66-------61  | [email protected]   | 596949200 |
| 1         | 77-------61  | [email protected]| 696949200 |

I want to get rows that have a distinct ‘phone’ and ’email’ (not a distinct combination) along with the data associated with the first instance (here dob as an example).

My desired output:

| id_number | phone        | email            | dob       |
| --        | ----------   | ----             | ----      |
| 6         | 04-------61  | [email protected]| 196949200 |
| 2         | 66-------61  | [email protected]   | 596949200 |
| 1         | 77-------61  | [email protected]| 696949200 |

Is there a way I can achieve this? I’ve tried using distinct and group by but I can’t see how they would be helpful in this case.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    After some Investigation the solution I came up with is below:

    SELECT t1.*
FROM `patientData` AS t1
LEFT OUTER JOIN `patientData` AS t2
  ON (t1.mobile = t2.mobile OR t1.email = t2.email) AND t1.id_number < t2.id_number
WHERE (t2.id_number IS NULL)

    Explanation: Return only the rows which do not have any id_numbers greater than it for each unique email and mobile


  2. 0

    DISTINCT (or GROUP BY) is a row operator that apply to all columns of the SELECT clause in the SELECT statement. If you want to operate your own specific distinct, you have to choose which data out of the distinct scope you need to get in the result. You can use either Max or MIN, or a random value…

    As an example :

    SELECT MIN(id), MIN(phone), email, MIN(dob)
FROM  "I have a table similar to below"
GROUP  BY email
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search