can someone please help me in this, I am getting the error

Column 'MoldInfo.SetID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

when I used this query

select *
from MoldInfo
group by MoldName
having count(distinct SetID) > 1

I want to have an output table with all the columns but some MoldName have repeated entries, I only want the mold name with latest date.

This is the image of the table 'MoldInfo'

CodePudding user response：

The GROUP BY is meant to be used with aggregate functions and the purpose of it is to define the set of rows that the aggregate function is applied to. Using it with SELECT * makes no sense and will result into an error.

Perhaps this will work for you:

select MoldName, count(distinct SetID)
from MoldInfo
group by MoldName
having count(distinct SetID) > 1

CodePudding user response：

Thank you all for your responses. I have been able to resolve the problem myself though.

    select distinct a.SetID, a.MoldName, a.StartDate from MoldInfo a
    where a.StartDate = (Select Max(StartDate) from MoldInfo c 
    where c.MoldName = a.MoldName) AND a.MoldName<>''
    order by a.MoldName

I am getting the desired output with distinct MoldName with lastest StartDate.

If you want all the columns

select distinct a.* from MoldInfo a
where a.StartDate = (Select Max(StartDate) from MoldInfo c 
where c.MoldName = a.MoldName) AND a.MoldName<>''
order by a.MoldName