[Solved] Find minimum datetime while using FK in two different tables

by


With 2 courses you are always going to get 2 rows when joining like this. It will give you the minimum date value for each course. The first way you can get a single row is to use TOP 1 in your query, which will simply give you the course with the earliest test date. The other way is to use a WHERE clause to filter it by a single course.

Please run this sample code with some variations of what you can do, notes included in comments:

CREATE TABLE #course ( id INT, name NVARCHAR(20) );

CREATE TABLE #Test
    (
      id INT ,
      courseId INT ,
      testDate DATETIME -- you shouldn't use a keyword for a column name
    );

INSERT  INTO #course
        ( id, name )
VALUES  ( 1, 'Maths' ),
        ( 2, 'Science' );

-- note I used DATEADD(HOUR, -1, GETDATE()) to simply get some random datetime values
INSERT  INTO #Test
        ( id, courseId, testDate )
VALUES  ( 1, 1, DATEADD(HOUR, -1, GETDATE()) ),
        ( 2, 1, DATEADD(HOUR, -2, GETDATE()) ),
        ( 3, 1, DATEADD(HOUR, -3, GETDATE()) ),
        ( 4, 2, DATEADD(HOUR, -4, GETDATE()) ),
        ( 5, 2, DATEADD(HOUR, -5, GETDATE()) ),
        ( 6, 2, DATEADD(HOUR, -6, GETDATE()) ),
        ( 7, 2, DATEADD(HOUR, -7, GETDATE()) ),
        ( 8, 2, DATEADD(HOUR, -8, GETDATE()) );

-- returns minumum date for each course - 2 rows
SELECT  MIN(t.testDate) AS TestDate ,
        t.courseId ,
        c.name
FROM    #Test t 
        -- used inner join as could see no reason for left join
        INNER JOIN #course c ON t.courseId = c.id
GROUP BY courseId , name;

-- to get course with minimum date - 1 row
SELECT TOP 1
        MIN(t.testDate) AS TestDate ,
        t.courseId ,
        c.name
FROM    #Test t 
        -- used inner join as could see no reason for left join
        INNER JOIN #course c ON t.courseId = c.id
GROUP BY t.courseId , c.name
ORDER BY MIN(t.testDate); -- requires order by

-- to get minimum date for a specified course - 1 row
SELECT  MIN(t.testDate) AS TestDate ,
        t.courseId ,
        c.name
FROM    #Test t 
        -- used inner join as could see no reason for left join
        INNER JOIN #course c ON t.courseId = c.id
WHERE t.courseId = 1 -- requires you specify a course id
GROUP BY courseId , name;

DROP TABLE #course;
DROP TABLE #Test;

13

solved Find minimum datetime while using FK in two different tables