SQL Select Question
-
Got myself a head scratcher here. I'm needing to pull employee names and pictures from a database, which I can do with the following select:
SELECT dbo.vwEmployee.BadgeName, dbo.Image.Photo, dbo.vwEmployee.Updated
FROM dbo.Image INNER JOIN
dbo.vwEmployee ON dbo.Image.OwnerGUID = dbo.vwEmployee.OwnerGUID
WHERE (dbo.vwEmployee.Department = 'IT')Problem is that the application that was used to store the employee information and photos also keeps every photo taken of the employee. I want to be able to return only the row with the most recent photo taken (or the first instance of the most recent update since some have multiple records with the same updated date). I know I can do this with the application I am writing to use this query, but I would rather let the SQL Server do most of the heavy lifting and to further narrow down my results.
-
Got myself a head scratcher here. I'm needing to pull employee names and pictures from a database, which I can do with the following select:
SELECT dbo.vwEmployee.BadgeName, dbo.Image.Photo, dbo.vwEmployee.Updated
FROM dbo.Image INNER JOIN
dbo.vwEmployee ON dbo.Image.OwnerGUID = dbo.vwEmployee.OwnerGUID
WHERE (dbo.vwEmployee.Department = 'IT')Problem is that the application that was used to store the employee information and photos also keeps every photo taken of the employee. I want to be able to return only the row with the most recent photo taken (or the first instance of the most recent update since some have multiple records with the same updated date). I know I can do this with the application I am writing to use this query, but I would rather let the SQL Server do most of the heavy lifting and to further narrow down my results.
Does the Image table have multiple records or multiple columns for the pictures? If record then does it have a time stamp? If so use a subquery to find the max time and pass that picture.
-
Got myself a head scratcher here. I'm needing to pull employee names and pictures from a database, which I can do with the following select:
SELECT dbo.vwEmployee.BadgeName, dbo.Image.Photo, dbo.vwEmployee.Updated
FROM dbo.Image INNER JOIN
dbo.vwEmployee ON dbo.Image.OwnerGUID = dbo.vwEmployee.OwnerGUID
WHERE (dbo.vwEmployee.Department = 'IT')Problem is that the application that was used to store the employee information and photos also keeps every photo taken of the employee. I want to be able to return only the row with the most recent photo taken (or the first instance of the most recent update since some have multiple records with the same updated date). I know I can do this with the application I am writing to use this query, but I would rather let the SQL Server do most of the heavy lifting and to further narrow down my results.
If you have a date field, then use
TOP 1andORDER BYupdate_dateDESC.CQ de W5ALT
Walt Fair, Jr., P. E. Comport Computing Specializing in Technical Engineering Software
-
Got myself a head scratcher here. I'm needing to pull employee names and pictures from a database, which I can do with the following select:
SELECT dbo.vwEmployee.BadgeName, dbo.Image.Photo, dbo.vwEmployee.Updated
FROM dbo.Image INNER JOIN
dbo.vwEmployee ON dbo.Image.OwnerGUID = dbo.vwEmployee.OwnerGUID
WHERE (dbo.vwEmployee.Department = 'IT')Problem is that the application that was used to store the employee information and photos also keeps every photo taken of the employee. I want to be able to return only the row with the most recent photo taken (or the first instance of the most recent update since some have multiple records with the same updated date). I know I can do this with the application I am writing to use this query, but I would rather let the SQL Server do most of the heavy lifting and to further narrow down my results.
You need to do a sub-select to get a list of the employees and the date of their most recent picture. Then you join to that on the EmployeeID (OwnerGuid) and the date.
select c.BadgeName, a.Photo, c.Updated
from Image a
join (select OwnerGUID, MAX(PictureDate) PictureDate from Image group by OwnerGUID) b on a.OwnerGUID = b.OwnerGUID and a.PictureDate = b.PictureDate
join vwEmployee c ON a.OwnerGUID = c.OwnerGUID
where c.Department = 'IT'