CODEWITHSUNDEEP
mysqlsql
dper
dper

Reputation: 904

How to select distinct values from a table

Hi I have a sql table which has the following entries.

Child_Name    Attempt_Number  Concept_ID   IsCompleted

 Class          NULL                C5         NULL

 Function       3                 C6           No

Function        4               C6           Yes

I want to get the result with the enties having distinct child_Name and maximum attempt_Number. The expected outcome is 

Child_Name    Attempt_Number  Concept_ID   IsCompleted

Class           NULL                 C5         NULL

Function        4                C6            Yes

The entry of function with lower attempt_Number is removed..

Can anybody help?

Upvotes: 3

Views: 82

Answers (3)

jarlh
jarlh

Reputation: 44696

Use a sub-query to find each child's max Attempt_Number. Join with that sub-query:

select t1.Child_Name, t1.Attempt_Number, t1.Concept_ID, t1.IsCompleted
from tablename t1
  join (select Child_Name, max(Attempt_Number) as Max_Attempt_Number
        from tablename
        group by Child_Name) t2
    on t1.Child_Name = t2.Child_Name and t1.Attempt_Number = t2.Max_Attempt_Number

Will show both rows if there's a tie (same max in two rows for a child.)

Upvotes: 1

Sougata Bose
Sougata Bose

Reputation: 31739

You can try with - 

select * from 
(select * from your_table order by attempt_Number DESC) tbl
group by child_name

Upvotes: 1

Nir Levy
Nir Levy

Reputation: 12953

you need to use max() function:

select * from yourTable where (child_name, attempt_number) in (
  select child_name, max(attempt_number) from yourTable group by child_name)
)

explanation: the inner table will give you the maximum attempt_number per child_name. since you want all columns and not only these two, you need the outer query to get them

Upvotes: 1

Related Questions