Need to create a Terdata Query


Need to create a Terdata Query

Hi All,

I need to create a teradata query.
The below is the senario:

I have a table name ABC.In that table i have a column student id, course id, course name, assigned date and completion date


IDN STUDENT_ID Course_Id Course_Name Assigned_date Com_date
1 10001 X XYZ 2-JAN 11-May
2 10001 X MLN 2-JAN 11- May
3 10002 X XYZ 2-JAN 13- Apr
4 10003 X MLN 2-JAN 18- Apr

I this table there is a course X which has 2 course in it XYZ and MLN .Some student has assigned MLN cousrse and some student XYZ course and some both.

i need a query when it runs it will provided me single record with all columns from tables for a particular student.(if that student has two records then the one he has completed recently that should only come and if both are completed on the same day then MLN rows should be taken)

Tags (1)
Junior Contributor

Re: Need to create a Terdata Query

See if this fits your requirement:

select * from tab
qualify row_number()
over (partition by student_id
order by com_date, case when course_name ='MLN' then 0 else 1 end) = 1