HELP with sql query involving two tables and a max date

Question

I have two tables notifications and mailmessages.

Notifications table
- NotifyTime
- NotifyNumber
- AccountNumber

MailMessages table
- id
- messageSubject
- MessageNumber
- AccountNumber

My goal is to create a single sql query to retrieve distinct rows from mailmessages WHERE the accountnumber is a specific number AND the notifynumber=messagenumber AND ONLY the most recent notifytime from the notifications table where the accountnumbers match in both tables.

I am using sqlexpress2008 as a back-end to an asp.net page. This query should return distinct messages for an account with only the most recent date from the notifications table.

Please help! I'll buy you a beer!!!

Answer 1

Try this...

SELECT MM.MaxNotifyTime, Notify.MaxNotifyTime
FROM MailMessages MM 
  INNER JOIN (SELECT Max(NotifyTime) MaxNotifyTime, AccountNumber
              FROM Notifications
              GROUP BY AccountNumber) Notify ON (MM.AccountNumber=Notify.AccountNumber)
WHERE (MM.AccountNumber=1)

Answer 2

SELECT MM.MessageNumber, MAX(N.NotifyTime) MaxTime
FROM MailMessages MM
INNER JOIN Notifications N
    ON MM.AccountNumber = N.AccountNumber AND MM.MessageNumber = N.NotifyNumber
WHERE MM.AccountNumber = 1
GROUP BY MM.MessageNumber

This limits to the given AccountNumber (=1) and outputs every associated MessageNumber together with the date of the most recent corresponding entry in Notifications.