Join two tables and then pull Distinct Records

I'm writing a VS 2010 program in C# and I've ran into a SELECT statement in SQL that is taking me to long to figure out and could use some help.

Table 1 - mailfiles
id,fname,lname,etc...

Table 2 - details,
id,timestamp,page_id,mailfile_id(FK),campaign_id

I want to pull the unique/distinct mailfile_id sorted by the most current timestamp and then join them to the mailfiles table to get the rest of my info.

I had something like this,

SELECT mailfiles.id,mailfiles.fname,mailfiles.lname,mailfiles.company2,mailfiles.city,etc...
FROM mailfiles
JOIN
(SELECT DISTINCT(details.mailfil开发者_如何学编程e_id)
FROM details
GROUP BY details.mailfile_id) as TMP
ON mailfiles.id = TMP.mailfile_id
ORDER BY TMP.mailfile_id DESC 

Which gets me the distinct/unique records but I don't have access to the details columns, which I want to display the timestamp.

Any help would be much appreciated.

Thanks

Nick

How about:

SELECT * 
FROM
(SELECT mailfile_id, Max(timestamp) m_timestamp FROM details GROUP BY mailfile_id) AS latest
INNER JOIN details on latest.mailfile_id = details.mailfile_id AND latest.m_timestamp = details.timestamp
INNER JOIN mailfiles ON mailfiles.id = details.mailfile_id
ORDER BY details.timestamp

There's an unresolved ambiguity in the case that there are two identical timestamps, but it seems that's something that would have to be solved in any case. For a given id, which of these 2 timestamps is actually the latest?

I'm afraid what you want to do is quite tricky since as you've found out the distinct operator works on all the fields you list in the query. If it works for you, you could return a list of ID's using your first query and then use these ID's to retrieve the details that your require in a second query. I know its two queries, but it may end up being faster than a large complex query anyway.

SELECT MAX (timestamp), fname, lname, ...
FROM mailfiles m, details d
WHERE m.id. = d.mailfiles_id 
GROUP BY m.id, fname, lname, ...

Hm - yes, I see the problem rising - do you need more than timestamp from the details? Then it get's more tricky.

SELECT m.*, d.* 
FROM mailfiles m, details d 
WHERE m.id = d.mailfiles_id 
  AND (d.mailfiles_id, d.timestamp) IN (
    SELECT mailfiles_id, MAX (timestamp) 
    FROM details 
    GROUP BY mailfiles_id);

If you need details from both tables.

I don't know whether your database supports ... and (a, b) in (SELECT c, d FROM ...; postgresql does.