How to select separate values ​​from a table

advertisements

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?


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