CODEWITHSUNDEEP
mysql
Trpimir
Trpimir

Reputation: 41

Retrieve one row from set result if value in certain column is duplicate

I would like to retrieve one record from table if there is duplicate in certain column like it follows:

------------------------
| id | year |  number  | 
| 1  | 2018 |    I     |
| 1  | 2018 |    II    |
| 1  | 2017 |    I     |
| 1  | 2016 |    II    |
| 2  | 2017 |    I     |
| 2  | 2017 |    II    |
| 2  | 2016 |    I     |
| 2  | 2016 |    II    |
| 2  | 2015 |    II    |
------------------------

desired output

------------------------
| id | year |  number  | 
| 1  | 2018 |    I     |
| 1  | 2017 |    I     |
| 1  | 2016 |    II    |
| 2  | 2017 |    I     |
| 2  | 2016 |    I     |
| 2  | 2015 |    II    |
------------------------

I've googled and tried tricks with 'group by + having' and tried creating index on those columns with 'insert ignore' combined but without avail. Anyone to say if this is even possible?

UPDATE: Finally came up with solution, I've created index on id and year, then made copy of existing table with 'INSERT IGNORE INTO ...'.

Upvotes: 1

Views: 29

Answers (3)

rwynn christian
rwynn christian

Reputation: 59

Try this

SELECT * FROM table_name GROUP BY id,year;

Note: it is not good practice to create tables without primary keys and use MySQL keywords for table names, such as year and number. If you still insist on using year and number always encapsulate them with `.

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You could use aggregation for your required data set as

select id, `year`, min(`number`)
from your_table
group by id, `year`
order by id,`year` desc

Demo

MIN() may take a string argument; in such cases, it returns the minimum string value

Upvotes: 1

Aman Deep
Aman Deep

Reputation: 380

you can try this to select unique values from table

SELECT DISTINCT column1, column2,.....columnN 
FROM table_name

Good Luck

Upvotes: 0

Related Questions