0

My Table looks like this.

Id   |  Name  |  Ref  |From 
10   |  Ant   |  100  | A
10   |  Ant   |  300  | A
2    |  Cat   |  90   | A
2    |  Cat   |  500  | A
3    |  Bird  |  150  | A

This is the result I want.

Id   |  Name  |  Ref  | From 
3    |  Bird  |  150  | A
2    |  Cat   |  500  | A
10   |  Ant   |  300  | A

My target is the highest Ref. Could you please tell me about how to write a sql query using pl/sql.

Bell Aimsaard
  • 483
  • 3
  • 5
  • 16
  • When you say your target is the highest ref, you're saying you want to select everything and order by the highest ref? Also, is From a column? – rma Jun 28 '17 at 05:13
  • This is basically the same question [you asked three hours earlier](https://stackoverflow.com/q/44792879/146325). Please don't ask the same question multiple times. It is disrespectful to the people who are trying to help you and it clutters up the site with noise. If your first question doesn't get the answer you need **revise the question** to make your requirements clearer and **engage with the responders** to explain why they haven't solved your problem. – APC Jun 28 '17 at 12:58

3 Answers3

0

You can use aggregation for this:

select id, name, max(ref) as ref, "From"
from your_table
group by id, name, "From";

Also, note the double quotes around from as it's a reserved keyword. I'd recommend using some other column name for it.

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
0

just use group by and max(), like this

select m.Id,m.Name,max(m.ref), m.From 
from myTable m 
group by m.Id,m.Name,m.From
order by m.Id
Vecchiasignora
  • 1,275
  • 7
  • 6
0

SELECT id, name, max(ref) FROM tablename GROUP BY name But change your column name 'From' because it is a reserved keyword in mysql.

Deepanshu
  • 142
  • 1
  • 12