CODEWITHSUNDEEP
mysqlsqlgroup-by
Taha Farooqui
Taha Farooqui

Reputation: 747

Condition on duplicate rows

I have 4 columns:

user id, start date (D/M/Y), type and name

user_id | start_date | type | name
1       | 1-01-2017  | 0    | Jack
1       | 1-01-2017  | 1    | Jack
1       | 2-01-2017  | 0    | Jack 
1       | 3-01-2017  | 0    | Jack
1       | 4-01-2017  | 0    | Jack 
1       | 5-01-2017  | 0    | Jack

I want to retrieve data by id ,type and start date .But the condition is, I want only single record, if date is same and priority is where type = 1 . Don't want duplicate dates. I already tried Group by but it shows only where type is 0.

I want Like this :

user_id | start_date | type | name
1       | 1-01-2017  | 1    | Jack
1       | 2-01-2017  | 0    | Jack 
1       | 3-01-2017  | 0    | Jack
1       | 4-01-2017  | 0    | Jack 
1       | 5-01-2017  | 0    | Jack

GROUP BY Result :

user_id | start_date | type | name
1       | 1-01-2017  | 0    | Jack
1       | 2-01-2017  | 0    | Jack 
1       | 3-01-2017  | 0    | Jack
1       | 4-01-2017  | 0    | Jack 
1       | 5-01-2017  | 0    | Jack

Upvotes: 2

Views: 48

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272006

Simply select the MAX(type) from your data:

SELECT user_id, start_date, MAX(type) AS x_type, name
FROM testdata
GROUP BY user_id , start_date, name

This returns one row per each user_id, start_date, name tuple:

| user_id | start_date | x_type | name |
+---------+------------+--------+------+
| 1       | 1-01-2017  | 1      | Jack |
| 1       | 2-01-2017  | 0      | Jack |
| 1       | 3-01-2017  | 0      | Jack |
| 1       | 4-01-2017  | 0      | Jack |
| 1       | 5-01-2017  | 0      | Jack |

Upvotes: 4

user9044104
user9044104

Reputation:

I don't understand what exactly are you looking for, but the following query, will give you the exact results you are looking for:

SELECT MAX(user_id) AS user_id, 
  start_date,
  MAX(type) AS type, 
  MAX(name) as name
FROM data AS d1  
GROUP BY start_date;

Results:

| user_id |           start_date | type | name |
|---------|----------------------|------|------|
|       1 | 2017-01-01T00:00:00Z |    1 | Jack |
|       1 | 2017-02-01T00:00:00Z |    0 | Jack |
|       1 | 2017-03-01T00:00:00Z |    0 | Jack |
|       1 | 2017-04-01T00:00:00Z |    0 | Jack |
|       1 | 2017-05-01T00:00:00Z |    0 | Jack |

Upvotes: 6

Related Questions