0

I am in an intro to databases class. I have to list the earliest contact date of an employee along with their contactID and contactType. The code I have that gives me the earliest date is:

SELECT MIN (Contact.ContactDate) AS FirstContact
FROM Contact;

If I try adding on the other columns such as:

SELECT MIN (Contact.ContactDate) AS FirstContact, ContactID, ContactType
FROM Contact;

I get an error message. I've tried nesting the select statements but that just turns into a bigger mess. I am obviously not using the correct syntax. I've rearranged everything and nothing is giving me the results I need.

Parzival
  • 2,051
  • 4
  • 14
  • 32
Ana
  • 1
  • 1
  • 1
    Hi @Ana, if you are receiving an error message, it is much more helpful if you also include the details of that error in your question – Craig Mar 25 '21 at 01:01
  • 1
    Sample data as text tables can also be helpful. – June7 Mar 25 '21 at 01:02
  • The error message I receive is "Your query does not include the specified expression 'ContactID' as part of an aggregate function" – Ana Mar 25 '21 at 01:02
  • Depending on exactly how you need to present the data results, either mine or Gordon's answer should get you there. Since you are just learning, it's useful to understand what that error means in terms of how your SELECT statement is structured. – Craig Mar 25 '21 at 01:06
  • Yeah, actually, I deleted my answer because I was probably misunderstanding. Presumably there must be a field in the Contact table to indicate the employee's ID? @Ana, it would probably be helpful to understand the relationship between the "Contact" table and employee's. Gordon's answer will get you the earliest contact record, however in terms of how you use the "SELEC TOP ...." will depend on whether you're displaying the information one-by-one (for example, if a user select's a particular employee), or you want to show a list of all employee's with their earliest contact details beside – Craig Mar 25 '21 at 01:22
  • Does this answer your question? [Top n records per group sql in access](https://stackoverflow.com/questions/41220690/top-n-records-per-group-sql-in-access) – June7 May 12 '21 at 20:26

1 Answers1

1

Instead, use order by and top:

SELECT TOP (1) c.*
FROM Contact as c
ORDER BY c.ContactDate;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786