CODEWITHSUNDEEP
mysqlsql
Prakash PV
Prakash PV

Reputation: 19

Need Help in creating a SQL Statement

Here is the table data

Name       Stage ID       Event ID     Attempt No            Score
Ramesh        1             1             1                    10
Ramesh        1             1             2                    20
Ramesh        2             1             1                    30
Suresh        1             1             1                    05
Suresh        2             1             1                    15
Suresh        2             2             1                    25
Suresh        2             2             2                    35
Suresh        2             2             3                    30

We have a Table with Name, Stage ID, Event ID, Attempt No, Score.

We want to group the data for Name, Stage ID, Event ID, Attempt No, Max(Score)

There can be records for every attempt under stage and Event and name and we need to take the max score and the same needs to be displayed.

The output should be :

Name       Stage ID       Event ID     Attempt No            Score
Ramesh        1             1             2                    20
Ramesh        2             1             1                    30
Suresh        1             1             1                    05
Suresh        2             1             1                    15
Suresh        2             2             2                    35

This would have one record for the combination of name + stage + Event

Upvotes: 0

Views: 34

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133380

You could use a inner join on the subquery for the max value you need

select yt.* 
from your_table yt
INNER JOIN ( 
    select Name, Stage_ID, Event_ID, Attempt_No, max(score) max_score 
    from your_table 
    group by  Name,  Stage_ID, Event_ID, Attempt_No
) t on yt.name  = t.name 
      and yt.Stage_ID = t.Stage_ID
          and yt.Event_ID = t.Event_ID 
            and yt.Attempt_No = t.Attempt_No 
              and yt.score  = t.max_score   join

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try below using correlated subquery

select * from tablename t1
where score in 
    (select max(score) from tablename t2 where t1.name=t2.name and t1.stage=t2.stage 
      and t1.eventid=t2.eventid)

Upvotes: 1

Related Questions