• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1272
  • Last Modified:

Query to find min/max date for when a list of items appeared in a table

Hello -

I have a table with name and dates in a table.   For each name I need to find the first and last date for which the name appears in the table.  I dont want to run any loops but for some reason can't warp my head around the query. I'll be storing the data in a temp table so if it's two queries and one as an update etc. thats fine.  I just want to avoid the performance hit of a loop or cursor.  


Name, First Date, Last Date
  • 2
1 Solution
Patrick MatthewsCommented:
Hello gigglick,

SELECT [Name], MIN([Date]) AS FirstDate, MAX([Date]) AS LastDate
FROM SomeTable


gigglickAuthor Commented:
oy vey...no more late night Thursdays for me!   Thanks so much - worked perfectly!!
Patrick MatthewsCommented:

You could use MIN and MAX functions as:

SELECT YourNameField, MIN(YourDateField) FROM yourTable GROUP BY YourNameField
SELECT YourNameField, MAX(YourDateField) FROM yourTable GROUP BY YourNameField

Giving you something along the lines of:

 SELECT DISTINCT tbl.YourNameField, qry1.MaxDate, qry2.MinDate
  FROM YourTable tbl
  INNER JOIN (SELECT TEAM_Code_Home, MAX(MAT_Date) AS MaxDate FROM YourTable GROUP BY YourNameField) qry1 ON tbl.YourNameField = qry1.YourNameField
INNER JOIN (SELECT TEAM_Code_Home, MIN(MAT_Date) AS MinDate FROM YourTable GROUP BY YourNameField) qry2 ON tbl.YourNameField = qry2.YourNameField

Hope that helps,
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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now