1

I have a table with field 'code' => '002' duplicate data.

---------------------------
| # | code | name | value |
----------------------------
| 1 | 001  |  A   |   2   |
| 2 | 002  |  B   |   4   |
| 3 | 002  |  B   |   6   |
| 4 | 003  |  C   |   3   |
| 5 | 004  |  D   |   1   |
---------------------------

All I want to do is to remove duplicate data and choose the higher value. The result is something like this.

---------------------------
| # | code | name | value |
----------------------------
| 1 | 001  |  A   |   2   |
| 2 | 002  |  B   |   6   |
| 3 | 003  |  C   |   3   |
| 4 | 004  |  D   |   1   |
---------------------------

I've tried query using DISTINCT but only show for single field. I really have no ideas to show all field with no duplicate data.

Please someone help me to figure this out.

thank you so much.

finsanurpandi
  • 25
  • 5
  • Is it safe to say that if a record has code `002`, the `name` field will be `B`? – Jacob Lambert May 10 '17 at 05:58
  • The column `#` is not fixed? If so, then is it ordered by `code` or row number of the table? – Shruti May 10 '17 at 06:00
  • @finsanurpandi How you are deciding if the record with value 6 has to come not the one with value 2?? How code 003 is having `#` value as 3 instead of 4, it's not stored in the table??Is it just a row number?? – Jibin Balachandran May 10 '17 at 06:02
  • What if code have 2 equal values? then they both are higher values right? in this case which one row do you need to choose ? – Oto Shavadze May 10 '17 at 06:06

1 Answers1

0

This query will give you the exact record (code + name) combination for which the value is maximum

Select a.* 
from
your_table_name a
inner join
(
Select code, max(value) as max_val
from your_table_name
group by code
) b
on a.code=b.code and a.value=b.max_val

Let me know if you have any questions

Strawberry
  • 33,750
  • 13
  • 40
  • 57
India.Rocket
  • 1,225
  • 1
  • 8
  • 11