Your
AllottedLicense column is not part of the
GROUP BY clause, so you need to specify an aggregate function for it:
SELECT Department, Max(AllottedLicense) As AllottedLicense, Server, MAX(DateAllotted) As DateAllotted
FROM AllottedLicense
GROUP BY Department, Server
Or you'll need to add it to the
GROUP BY clause:
SELECT Department, AllottedLicense, Server, MAX(DateAllotted) AS DateAllotted
FROM AllottedLicense
GROUP BY Department, AllottedLicense, Server
If you're looking to select the last license by date, you'll need a different solution:
WITH cte As
(
SELECT
Department,
Server,
AllottedLicense,
DateAllotted,
ROW_NUMBER() OVER (PARTITION BY Department, Server ORDER BY DateAllotted DESC) As RN
FROM
AllottedLicense
)
SELECT
Department,
Server,
AllottedLicense,
DateAllotted
FROM
cte
WHERE
RN = 1
;
Submit an article or tip