Help with SQL query

Question

Can anybody help me with the following SQL command please? I am not very experienced in SQL.

I have 2 tables:

Table Weighings with columns:

• WeighingId
• FileId
• MyDateTime

Table Files with columns:

• FileId
• Name

Table Weighings contains information when a specified file has been weighed. I need to make a list of last weighings of individual files, grouped by a file name. The final table would contain Weighing.Id, file name (File.Name) and second column when this file was weighed last time MAX(Weighings.MyDateTime).

There can be duplicate file names with different File.Id and I need to group all files with same name (so I can't group on File.FileId).

I was trying to use this code, but it doesn't work:

SELECT W.WeighingId AS WeighingId, MAX(W.MyDateTime) AS MaxMyDateTime
FROM Files F INNER JOIN Weighings W ON W.FileId = F.FileId
GROUP BY F.Name
ORDER BY F.Name

Thanks for your help, Petr

Answer 1

SELECT F.Name, 
       MAX(W.MyDateTime) AS MaxMyDateTime
FROM Files F INNER JOIN Weighings W ON W.FileId = F.FileId
GROUP BY F.Name
ORDER BY F.Name

Answer 2

"The final table would contain first column with a file name (File.Name) and second column when this file was weighed last time MAX(Weighings.MyDateTime)."

Your SQL retuns W.WeighingId as first column, not File.Name.

Fix that and it should work.

Answer 3

Something like:

select WeighingID
  , f.Name
  , f.FileID
  , MaxMyDateTime = (SELECT MAX(Weighings.MyDateTime) FROM Files F INNER JOIN Weighings ON Weighings.FileId = Files.FileId WHERE File.Name = f.Name)
from Weighings w
  inner join Files f
    on w.fileid=f.fileid

Answer 4

My appologies, I need to get Weighing.Id of that last weighings, so the final table should contain also Weighing.WeighingId.

Answer 5

It should be as:

SELECT 
    F.Name, 
    (SELECT TOP 1 WeighingId FROM Weighings WHERE MyDateTime = MAX(W.MyDateTime)),
    MAX(W.MyDateTime) AS MaxMyDateTime 
FROM Files F 
    INNER JOIN Weighings W ON W.FileId = F.FileId
GROUP BY F.Name
ORDER BY F.Name

You do not need F.Name to be in the select list (the opposite is not allowed as in WeighingId in you example). I guess the problem was how you could aggregate WeighingId to select.