SQL Group By Query Earliest Date

Hey Everyone,

I have been spinning my wheels on a SQL Query I have been working on. The below query returns several fields with a Group By. The "When_Finished" column has multiple date values in a "dd/mm/yyy hh:mm:ss" format. I need the query to only return the lowest date and time value. I have tried MIN() to accomplish this, but it continues to return all dates

Example:

Details | Member_Group | Session_Name | Percentage_Score | When Finished

11111  | Group 1            | Test 1             | 90                        | 01/01/13 5:15PM
11111  | Group 1            | Test 1             | 0                          | 01/01/13 4:15PM
11111  | Group 1            | Test 2             | 100                      | 01/02/13 1:00PM
11111  | Group 1            | Test 3             | 85                        | 01/04/13 12:15PM

What I need returned is:

11111  | Group 1            | Test 1             | 0                          | 01/01/13 4:15PM
11111  | Group 1            | Test 2             | 100                      | 01/01/13 1:00PM
11111  | Group 1            | Test 3             | 85                        | 01/04/13 12:15PM

SELECT     dbo.G_Participant.Details, A_Result.Member_Group, dbo.G_Schedule.Session_Name AS Expr1, A_Result.Percentage_Score, A_Result.When_Finished
FROM         dbo.G_Participant INNER JOIN
                      dbo.A_Result AS A_Result ON dbo.G_Participant.Details = A_Result.Participant_Details INNER JOIN
                      dbo.G_Schedule ON dbo.G_Participant.Participant_ID = dbo.G_Schedule.Participant_ID AND A_Result.Session_MID = dbo.G_Schedule.Session_MID AND 
                      A_Result.Session_LID = dbo.G_Schedule.Session_LID INNER JOIN
                      dbo.G_Group ON dbo.G_Schedule.Group_ID = dbo.G_Group.Group_ID
WHERE     (A_Result.Status = 2)
GROUP BY dbo.G_Participant.Details, A_Result.Member_Group, A_Result.Percentage_Score, dbo.G_Schedule.Session_Name, A_Result.When_Finished
HAVING      (A_Result.Member_Group LIKE 'Claims Ownership%')
ORDER BY dbo.G_Participant.Details

Open in new window

Kds4evrAsked:
Who is Participating?
 
chaauConnect With a Mentor Commented:
Sorry. I can see now. It looks like you also want to select the minimum percentage score (is it so?). In this case, modify the query like this:
SELECT     dbo.G_Participant.Details, A_Result.Member_Group, dbo.G_Schedule.Session_Name AS Expr1, min(A_Result.Percentage_Score), min(A_Result.When_Finished)
FROM         dbo.G_Participant INNER JOIN
                      dbo.A_Result AS A_Result ON dbo.G_Participant.Details = A_Result.Participant_Details INNER JOIN
                      dbo.G_Schedule ON dbo.G_Participant.Participant_ID = dbo.G_Schedule.Participant_ID AND A_Result.Session_MID = dbo.G_Schedule.Session_MID AND 
                      A_Result.Session_LID = dbo.G_Schedule.Session_LID INNER JOIN
                      dbo.G_Group ON dbo.G_Schedule.Group_ID = dbo.G_Group.Group_ID
WHERE     (A_Result.Status = 2)
GROUP BY dbo.G_Participant.Details, A_Result.Member_Group, dbo.G_Schedule.Session_Name
HAVING      (A_Result.Member_Group LIKE 'Claims Ownership%')
ORDER BY dbo.G_Participant.Details

Open in new window

0
 
chaauCommented:
You need to remove A_Result.When_Finished from the group by, like this:
SELECT     dbo.G_Participant.Details, A_Result.Member_Group, dbo.G_Schedule.Session_Name AS Expr1, A_Result.Percentage_Score, min(A_Result.When_Finished)
FROM         dbo.G_Participant INNER JOIN
                      dbo.A_Result AS A_Result ON dbo.G_Participant.Details = A_Result.Participant_Details INNER JOIN
                      dbo.G_Schedule ON dbo.G_Participant.Participant_ID = dbo.G_Schedule.Participant_ID AND A_Result.Session_MID = dbo.G_Schedule.Session_MID AND 
                      A_Result.Session_LID = dbo.G_Schedule.Session_LID INNER JOIN
                      dbo.G_Group ON dbo.G_Schedule.Group_ID = dbo.G_Group.Group_ID
WHERE     (A_Result.Status = 2)
GROUP BY dbo.G_Participant.Details, A_Result.Member_Group, A_Result.Percentage_Score, dbo.G_Schedule.Session_Name
HAVING      (A_Result.Member_Group LIKE 'Claims Ownership%')
ORDER BY dbo.G_Participant.Details

Open in new window

0
 
Kds4evrAuthor Commented:
Thanks chaau,

I gave it a whirl and still the same thing. Displays both the low and the high date on the return. Is a (SELECT TOP 1 .....) AS When_Finished_Top perhaps the approach on this? I had tried a few vairations, but I cannot get the where clause right.
0
 
Kds4evrAuthor Commented:
That did it! Perfect thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.