query function that results in latest date

25 pts.
Tags:
Database
Have 2 linked databases, one with name/address/ect, the second with dates service was provided for each address. I would like to query with the result being the address and other data with the date of latest service. I don't know the function that will result in only the latest date. I can get > greater than dates or all dates, but haven't been able to just get the latest date.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Did you try a <b>SELECT MAX(MyDateField) </b> ?

———-
Something like this:

select *, (select max(date) from second_tab as st where st.address=ft.address)
from first_tab as ft

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • JennyMack
    Howdy there, Could you please be more specific? What software are you using? Thanks, Jenny Community Manager
    4,280 pointsBadges:
    report
  • Wbfiv
    Sorry, I am using Access database. I have one database with customer information linked to another that show the multiple dates that service was performed for each customer. I want to come up with a list of customers and their information that shows only the latest date that service was performed for each. I don't know how to filter the dates to select only the latest. thanks
    25 pointsBadges:
    report
  • DPaxton
    Check out this link on working with duplacates. http://office.microsoft.com/en-us/access/HA010345581033.aspx?pid=CL100570041033#12 In the query sort buy customer then sort the date field desending and have it remove the dup's.
    180 pointsBadges:
    report
  • Wbfiv
    Thanks for the answer on duplicates, but I am not trying to remove duplicate dates, just the dates prior to the most recent. Hope this helps.
    25 pointsBadges:
    report
  • Kccrosser
    The "cleanest" way is to use a function that returns the latest date of service, but you can achieve the same result using a "not exists" clause. Assume you have: MyAddrTable ( TheAccount varchar(255), TheAddress varchar(255)); MyServiceTable ( TheAccount varchar(255), DateOfService datetime); Then: select addr.TheAddress, mst1.DateOfService from MyAddrTable addr with (nolock) join MyServiceTable mst1 with (nolock) on (mst1.TheAccount = addr.TheAccount) where not exists (select 1 from MyServiceTable mst2 with (nolock) where mst2.TheAccount = mst1.TheAccount and mst2.DateOfService > mst1.DateOfService) This will return one row for each of the address records, with the latest date of service for that address.
    3,830 pointsBadges:
    report
  • carlosdl
    How about:
    SELECT a.name, a.address, max(s.DateOfService)
    FROM MyAddrTable a JOIN MyServiceTable s
    ON a.address = s.address
    GROUP BY a.name, a.address
    ?
    69,510 pointsBadges:
    report
  • carlosdl
    Something went wrong with the formatting... How about something like this ?
    SELECT a.name, a.address, max(s.DateOfService)
    FROM MyAddrTable a JOIN MyServiceTable s
    ON a.address = s.address
    GROUP BY a.name, a.address
    69,510 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following