Max Date function?? 1

[Naoise]

I have a slightly complicated SQL statement that returns a recordset like

ID | SentDate
1 | 2005-03-14 17:49:01.000
1 | 2005-03-15 16:49:01.000
2 | 2005-03-10 17:49:01.000
3 | 2005-03-12 17:49:01.000
4 | 2005-03-15 16:49:01.000
4 | 2005-03-15 17:49:01.000

How do I return the latest SendDate for each result? I am already using ORDER BY for the ID.

Thanks for replies,
Naoise

 

[r937]

select ID
     , SentDate                       
  from daTable as foo
 where SentDate
     = ( select max(SentDate)
           from daTable
          where ID = foo.ID )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[PHV]

Something like this ?
SELECT ID, MAX(SentDate)
FROM yourTable
GROUP BY ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Naoise]

Hi that won't work given the situation I have...

tblSent
JobID, ContactID, SentDate

tblSuppliers
SupplierID, CompanyName

tblSupplierContacts
SupplierID, ContactID

tblContacts
ContactID, Email

SELECT Distinct(CompanyName), b.SupplierID, c.Email, c.ContactID, CONVERT(varchar, SentDate, 101) as SentDate
FROM tblSent a, tblSupplierContacts b, tblContacts c, tblSuppliers d
WHERE a.ContactID = b.ContactID
AND b.ContactID = c.ContactID
AND b.SupplierID = d.SupplierID
AND a.QuoteID = 44
ORDER BY CompanyName

This gives records sent to the same person on the same date. The max(SentDate) doesn't work. Thanks for replies,

Naoise

 

[Naoise]

Sorry correct SQL should read

SELECT Distinct(CompanyName), b.SupplierID, c.Email, c.ContactID, CONVERT(varchar, SentDate, 101) as SentDate
FROM tblSent a, tblSupplierContacts b, tblContacts c, tblSuppliers d
WHERE a.ContactID = b.ContactID
AND b.ContactID = c.ContactID
AND b.SupplierID = d.SupplierID
AND a.JobID = 44
ORDER BY CompanyName


max(SentDate) does not work at all on my datetime field.

 

[r937]

that won't work given the situation i have"

okay, two comments

1. you did a poor job of explaining your real situation, which means that the solution that you were given will require some adaptation to fit your real situation

2. the max(SentDate) solution will work, if you take the time to understand how it works and apply it to your situation

by the way, DISTINCT is not a function



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Naoise]

Two retorts.

1)I was trying to simplify the situation as I feel that the work to adapt the response should be up to me. I try not to make contributors have to try and understand the ins and outs of my particular situation if it can be avoided at all. I thought this might be the case but it ended up not being so.

2)I take plenty of time to follow up on responses and do as much as I can and not make posters do all the work.

Thank you for your replies.

 

[PHV]

A starting point:
SELECT d.CompanyName, b.SupplierID, c.Email, a.ContactID, MAX(CONVERT(varchar, a.SentDate, 101)) as SentDate
FROM tblSent a
INNER JOIN tblSupplierContacts b ON a.ContactID = b.ContactID
INNER JOIN tblContacts c ON b.ContactID = c.ContactID
INNER JOIN tblSuppliers d ON b.SupplierID = d.SupplierID
WHERE a.QuoteID = 44
GROUP BY d.CompanyName, b.SupplierID, c.Email, a.ContactID
ORDER BY CompanyName


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Naoise]

Thanks very much, this seems to have done the trick.