Results 1 to 2 of 2
  1. #1
    Join Date
    May 2012
    Posts
    132

    Unanswered: Student/lecture latest query,

    Lectures presented by all the students during the last year has been stored in a talbe named: studentlectures, with these fields : ID, studentname, lecturesubject, lecturedate
    For example:
    1, john, mathematics, 2/3/2012
    2, jack, chemistry, 10/3/2012 ,
    3,john, engineering, 15/3/2012
    4,jack ,chemisty,20/3/2012
    5,john,politics,30/3/2012
    6,john,engineering, 13/4/2012
    7,jack,politics,15/4/2012
    We want a query to show the latest record for each student with a specific lecture. For the above example the result will be:
    1, john, mathematics, 2/3/2012
    4, jack , chemisty,20/3/2012
    5,john, politics,30/3/2012
    6, john, engineering, 13/4/2012
    7, jack,politics,15/4/2012
    Previous subject records by each student are not shown, and only the latest one is shown.
    Thank you
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    With lectures being the name of the table, try:
    Code:
    SELECT lectures.ID, a.studentname, a.lecturesubject, a.lecturedate
    FROM Lectures INNER JOIN 
        ( SELECT Lectures.studentname, Lectures.lecturesubject, Max(Lectures.lecturedate) AS lecturedate
          FROM Lectures
          GROUP BY Lectures.studentname, Lectures.lecturesubject
        ) AS a
    ON Lectures.studentname = a.studentname AND 
       Lectures.lecturesubject = A.lecturesubject AND 
       Lectures.lecturedate = a.lecturedate
    ORDER BY a.lecturedate;
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •