Click here to Skip to main content
14,829,523 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
you have tried to execute a query that does not include a specific experession in allotted license as a part of function
i am getting this error when i tried to take latest datetime data depends on the allotted license column

What I have tried:

SELECT  Department,AllottedLicense,Server, MAX(DateAllotted) AS DateAllotted from AllottedLicense GROUP BY Department,Server 
Posted
Updated 5-Mar-21 4:01am

Your AllottedLicense column is not part of the GROUP BY clause, so you need to specify an aggregate function for it:
SQL
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:
SQL
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:
SQL
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
;
   
Comments
Maciej Los 5-Mar-21 17:01pm
   
5ed!
We can't tell you how to fix this: we have no access to your DB.

So, it's going to be up to you. Start by checking your connection string is to the right DB, then look at the definition of the AllottedLicence table and check if the column names match exactly those you are trying to select.

At a guess, you are reporting the error message wrong, as the AllottedLicence column is not a part of the GROUP BY or covered by an aggregate function. This may help: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]
   
Comments
Maciej Los 5-Mar-21 17:02pm
   
5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900