CODEWITHSUNDEEP
sqlsql-servermax
hollyquinn
hollyquinn

Reputation: 652

SQL Get Most Recent Date

I am hoping someone can help me. I'm not that good with SQL. I've looked at countless other posts and just can't figure it out. I have the following data and need to get the most recent date. That would be 09/01/1993.

524 | David | NULL | 1991 | 01 | H | 1991-07-01 00:00:00.000

524 | David | NULL | 1992 | 01 | H | 1992-07-01 00:00:00.000

524 | David | NULL | 1993 | 09 | H | 1993-09-01 00:00:00.000

I've tried the following query, but it brings back no results. Can anyone tell me what I'm doing wrong?

SELECT student_crs_hist.id_num,   
     name_format_view.last_first_middle_suf,   
     year_term_table.pesc_session_type,   
     student_crs_hist.yr_cde,   
     student_crs_hist.trm_cde, 
     student_crs_hist.TRANSACTION_STS,
     year_term_table.TRM_BEGIN_DTE
   FROM student_crs_hist,   
     name_format_view,   
     year_term_table
   WHERE  
   student_crs_hist.id_num = name_format_view.id_num
     and student_crs_hist.yr_cde = year_term_table.yr_cde 
     and student_crs_hist.trm_cde = year_term_table.trm_cde 
     and student_crs_hist.TRANSACTION_STS <> 'D' 
     and student_crs_hist.id_num = 524
     and year_term_table.TRM_BEGIN_DTE = (select max(year_term_table.TRM_BEGIN_DTE) from year_term_table)
Group By
     student_crs_hist.id_num,   
     name_format_view.last_first_middle_suf,   
     year_term_table.pesc_session_type,   
     student_crs_hist.yr_cde,   
     student_crs_hist.trm_cde, 
     student_crs_hist.TRANSACTION_STS, 
     year_term_table.TRM_BEGIN_DTE

Upvotes: 0

Views: 177

Answers (3)

FutbolFan
FutbolFan

Reputation: 13763

You will need to use MAX function for columns that do not have same values across same id_num:

SELECT student_crs_hist.id_num
    ,name_format_view.last_first_middle_suf
    ,year_term_table.pesc_session_type
    ,max(student_crs_hist.yr_cde)
    ,max(student_crs_hist.trm_cde)
    ,student_crs_hist.TRANSACTION_STS
    ,max(year_term_table.TRM_BEGIN_DTE)
FROM student_crs_hist
    ,name_format_view
    ,year_term_table
WHERE student_crs_hist.id_num = name_format_view.id_num
    AND student_crs_hist.yr_cde = year_term_table.yr_cde
    AND student_crs_hist.trm_cde = year_term_table.trm_cde
    AND student_crs_hist.TRANSACTION_STS <> 'D'
    AND student_crs_hist.id_num = 524
    AND year_term_table.TRM_BEGIN_DTE = (
        SELECT max(year_term_table.TRM_BEGIN_DTE)
        FROM year_term_table
        )
GROUP BY student_crs_hist.id_num
    ,name_format_view.last_first_middle_suf
    ,year_term_table.pesc_session_type
    ,student_crs_hist.TRANSACTION_STS

Upvotes: 0

Carl
Carl

Reputation: 324

    SELECT student_crs_hist.id_num,   
     name_format_view.last_first_middle_suf,   
     year_term_table.pesc_session_type,   
     student_crs_hist.yr_cde,   
     student_crs_hist.trm_cde, 
     student_crs_hist.TRANSACTION_STS,
     year_term_table.TRM_BEGIN_DTE
   FROM student_crs_hist,   
     name_format_view,   
     year_term_table
   JOIN (
       SELECT student_crs_hist.id_num, MAX(year_term_table.TRM_BEGIN_DTE) AS MaxDate
       FROM student_crs_hist,   
         name_format_view,   
         year_term_table
       WHERE  
       student_crs_hist.id_num = name_format_view.id_num
         and student_crs_hist.yr_cde = year_term_table.yr_cde 
         and student_crs_hist.trm_cde = year_term_table.trm_cde 
         and student_crs_hist.TRANSACTION_STS <> 'D' 
         and student_crs_hist.id_num = 524
         and year_term_table.TRM_BEGIN_DTE = (select max(year_term_table.TRM_BEGIN_DTE) from year_term_table)
       GROUP BY student_crs_hist.id_num
   ) MaxDateJoin ON student_crs_hist.id_num = MaxDateJoin.id_num AND year_term_table.TRM_BEGIN_DT = MaxDateJoin.MaxDate
   WHERE  
   student_crs_hist.id_num = name_format_view.id_num
     and student_crs_hist.yr_cde = year_term_table.yr_cde 
     and student_crs_hist.trm_cde = year_term_table.trm_cde 
     and student_crs_hist.TRANSACTION_STS <> 'D' 
     and student_crs_hist.id_num = 524
     and year_term_table.TRM_BEGIN_DTE = (select max(year_term_table.TRM_BEGIN_DTE) from year_term_table)
Group By
     student_crs_hist.id_num,   
     name_format_view.last_first_middle_suf,   
     year_term_table.pesc_session_type,   
     student_crs_hist.yr_cde,   
     student_crs_hist.trm_cde, 
     student_crs_hist.TRANSACTION_STS, 
     year_term_table.TRM_BEGIN_DTE

Upvotes: 0

dnoeth
dnoeth

Reputation: 60502

You probably want the highest date, but all other columns, too.

You need a Windowed Aggregate Function for this:

SELECT *
FROM 
 (
   SELECT student_crs_hist.id_num,   
        name_format_view.last_first_middle_suf,   
        year_term_table.pesc_session_type,   
        student_crs_hist.yr_cde,   
        student_crs_hist.trm_cde, 
        student_crs_hist.TRANSACTION_STS,
        year_term_table.TRM_BEGIN_DTE,
    -- group maximum = maximum date per id_num
        MAX(TRM_BEGIN_DTE) OVER (PARTITION BY student_crs_hist.id_num) AS maxDate
      FROM student_crs_hist,   
        name_format_view,   
        year_term_table
      WHERE  
      student_crs_hist.id_num = name_format_view.id_num
        AND student_crs_hist.yr_cde = year_term_table.yr_cde 
        AND student_crs_hist.trm_cde = year_term_table.trm_cde 
        AND student_crs_hist.TRANSACTION_STS <> 'D' 
        AND student_crs_hist.id_num = 524
 ) AS dt
WHERE TRM_BEGIN_DTE = maxDate  -- only the rows with the maximum date

Upvotes: 2

Related Questions