Clever Geek Handbook

SQL Query: List the last weightings of individual files, grouped by file name

I need help with the following SQL command.

I have 2 tables:

Weights table with columns:

  • WeighingId
  • FILEID
  • MyDateTime

Table files with columns:

  • FILEID
  • name

Weighing tables contain information when the specified file was weighed. I need to make a list of the last weightings of individual files, grouped by file name. The final table will contain Weighing.Id, the file name (File.Name) and the second column when this file was last weighed MAX (Weighings.MyDateTime).

There may be duplicate filenames with different File.Id, and I need to group all files with the same name (so I cannot group by File.FileId).

I tried 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
0


source to share


5 answers 


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


+3


source


"The final table will contain the first column for the file name (File.Name) and the second column for when this file was last weighed by MAX (Weighings.MyDateTime).

Your SQL is reconfiguring W.WeighingId as the first column, not File.Name.



Fix this and it should work.

+1


source


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
0


source


My addictions, I need to get Weighing.Id from the last weighings, so the final table should also have Weighing.WeighingId.

0


source


It should be like:

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 don't need F.Name to be in the select list (the opposite is not valid, as in the WeighingId in your example). I think the problem was how you could combine the WeighingId for the selection.

0


source






More articles:


All Articles