Find Minimum Datetime While Using Fk In Two Different Tables

I have 2 tables: COURSE ------ Id Name TEST ------ Id CourseId (FK to `COURSE.ID`) DATETIME NUMBERS Suppose COURSE table with ID 1,2 (only 2 columns) and TEST table with 8 numbe

Solution 1:

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:

CREATETABLE #course ( id INT, name NVARCHAR(20) );

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

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

-- note I used DATEADD(HOUR, -1, GETDATE()) to simply get some random datetime valuesINSERTINTO #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 rowsSELECTMIN(t.testDate) AS TestDate ,
        t.courseId ,
        c.name
FROM    #Test t 
        -- used inner join as could see no reason for left joinINNERJOIN #course c ON t.courseId = c.id
GROUPBY courseId , name;

-- to get course with minimum date - 1 rowSELECT TOP 1MIN(t.testDate) AS TestDate ,
        t.courseId ,
        c.name
FROM    #Test t 
        -- used inner join as could see no reason for left joinINNERJOIN #course c ON t.courseId = c.id
GROUPBY t.courseId , c.name
ORDERBYMIN(t.testDate); -- requires order by-- to get minimum date for a specified course - 1 rowSELECTMIN(t.testDate) AS TestDate ,
        t.courseId ,
        c.name
FROM    #Test t 
        -- used inner join as could see no reason for left joinINNERJOIN #course c ON t.courseId = c.id
WHERE t.courseId =1-- requires you specify a course idGROUPBY courseId , name;

DROPTABLE #course;
DROPTABLE #Test;

Solution 2:

In my understanding, you want to return the minimum date from the entire table with the course details of that day. Please try the below script

SELECT TOP 1 MIN(t.testDate) OVER (ORDERBY t.testDate) AS TestDate ,
        t.courseId ,
        c.name
FROM    Test t 
        INNER JOIN course c ON t.courseId = c.id
ORDERBY t.testDate