SQL Distinct and non-distinct column selection

SQL distinct for 2 fields in a database

The above link I believe is not too far from what I'开发者_C百科m looking for. There was a post talking about what I believe I need. It says that I should have my fields that do NOT need to be distinct in an aggregate and then the ones I want distinct should be in a group by clause.

The following SQL which I did NOT write works for giving us back the data we want. I've added the line: group by pf.PatientID, pf.SubjectID

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[getResearchAssistantProgressReportByStudyAndLoation]
(   
    @StudyId int,
    @LocationID int,
    @ResearchAssistantID int,
    @StartDate datetime,
    @EndDate datetime
)
RETURNS TABLE 
AS
RETURN 
(

Select 
    pf.PatientID, pf.SubjectID, pf.ResearchAssistantID,
    study.form, pf.dateAvailable, pf.DateComplete, 
    CAST((CASE WHEN pf.dateavailable + convert(int,study.daystoexpire,101) < GETDATE() and pf.dateComplete is null then 'True' else 'False' END) AS varchar(10)) as Expired
FROM 
    PatientForms pf
    INNER JOIN (SELECT * FROM getFormsINStudy(@StudyId)) as study
    on pf.formID = study.FormID
    INNER JOIN UserLocations ul on pf.patientid = ul.userid
WHERE 
    ul.LocationID = @LocationID
    and (ResearchAssistantID = @ResearchAssistantID or @ResearchAssistantID = -1)
    and (pf.DateAvailable + convert(int,study.daystoexpire,101)) > @StartDate
    and (pf.DateAvailable + convert(int,study.DAYSTOEXPIRE,101)) < @EndDate
group by pf.PatientID, pf.SubjectID
)

This gives me an error:

Msg 8120, Level 16, State 1, Procedure getResearchAssistantProgressReportByStudyAndLoation, Line 20
Column 'PatientForms.ResearchAssistantID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I get why I have this error, it's not in an aggregate function. What I'm not sure of how to do is to get it IN an aggregate, that to my knowledge would change the results of my query.

Any help anybody can and is willing to offer is greatly appreciated!

---

Whatever you have in a select statement that isn't an aggregate should show up in the group by area as well. If you have

 Select A, B , C , D = Sum (E) From Table 
 Group By A, B, C

Notice whatever isn't aggregated ends up in the Group By Area. So, pf.ResearchAssistantID must be in the Group By Clause. And it should run fine.

---

You have to group by everything in the select list that is not an aggregate. That's fundamental SQl as implemented in just about every database except mySQL. If you don't have a field in the group by and the values for two records are different, how would it know which one to put into the result set? You must specify which one you want. Your only choices are: Include the other field in the group by Specify using an aggregate which value you want selected. Usually people use min() or max().

Even in mySQl you should use the aggreagate approach or add them tothe group by as it otherwise will pick out a value for you and it may not be the one you need.