Distinct Record

[sr_aneesh]

I have the following records

ID Name Class Marks
1 Steff 2 20
2 Mark 2 30
3 Ronnie 4 60
4 Job 1 25
5 John 4 15
6 Len 2 10
7 Robert 4 15

what query should be written to retreive distinct Class with Maximum Marks, Name, ID

That is
The Out Put should be

Name Marks Class ID
Ronnie 60 4 3
Job 25 1 4
Mark 30 2 2

Thanx in advance
Aneesh

[erickwidya]

..written to retreive distinct Class with Maximum Marks, Name, ID is what u mean just Maximum Marks? or Maximum from Marks, Name, ID?
normally if u want to have distinct records..ur table must have more than one record that point to same field..

could u provide more records for ur table..?
regards

[Madhi]

Hi sr_aneesh, Your data are not enough to write the query. Give full data from the table.

Madhivanan

[hspc]

Hi
this query returns the required results :

SELECT table1.ID, table1.name, table1.class, table1.mark
FROM table1
inner join (Select class,max(mark) as mm from table1 group by class ) as S ON
table1.class = s.class and table1.mark=s.mm

results :
ID name class mark
2 Mark 2 30
3 Ronnie 4 60
4 Job 1 25

[Madhi]

Hi hspc, Thanks. Your query works well. I write the following query which gives the same result

SELECT t1.ID, t1.name, t1.class, t1.mark
FROM (SELECT table11.ID, table11.name, table11.class, table11.mark
from table11) as t1,(SELECT table11.ID, table11.name, table11.class, table11.mark
from table11) as t2
where t1.id=t2.id
group by t1.ID, t1.name, t1.class, t1.mark
having t1.mark=max(t2.Mark)


Madhivanan

[erickwidya]

now i can see what sr_aneesh wants..

thanks hspc