Search code examples
sqldatabaseoracle10g

Need correction of SQL query

I want to find the semecode where mindate only

My query is

  SELECT stdcode, Degree_Code, TODATE, Gradediv, Degree_Name, semcode, mindate
  FROM CHKLIST
  WHERE stdcode LIKE '%02-11036%'
  GROUP BY STDCODE, Degree_Code, TODATE, Gradediv, Degree_Name, semcode, mindate;

Expected result like out put should like this image attached:

enter image description here

and sample data attached here

https://docs.google.com/spreadsheets/d/1NrU1NzVucmMLuwLXIf_nwvUbi6vjvEDkZDovsu5panA/edit?usp=sharing

Solution

  • It would be easier if you had some table with at least start dates for each semester, but if you dont' here is what I have

    select t1.*, cl.semcode 
  from (SELECT STDCODE, Degree_Code, TODATE, Gradediv, Degree_Name, min(mindate) mindate
         FROM CHKLIST
        GROUP BY STDCODE, Degree_Code, TODATE, Gradediv, Degree_Name) t1
  join CHKLIST cl
    on cl.stdcode = t1.stdcode 
       and cl.degree_code = t1.degree_code 
       and t1.gradediv = cl.gradediv 
       and t1.degree_name = cl.degree_name
       and t1.mindate = cl.mindate

    The most complicated aspect is you have semcode col with text values which are hard to compare.

    the DB cannot say whether string "BAD-SUMMER-02" is greater than "BAD-FALL-01". So I carried out semcode column into joined table and then join aggregated values from the subquery with same table again.