SQL MIN () function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL MIN () function

Author

Topic

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-11-01 : 11:47:42

SELECT TOP (100) PERCENT dbo.syStudent.SSN, dbo.syStudent.LastName, dbo.syStudent.FirstName, dbo.AdEnroll.CreditsEarned, dbo.SySchoolStatus.Code,
dbo.SySchoolStatus.Descrip, dbo.AdProgramVersion.Code AS Expr1, dbo.AdProgramVersion.Descrip AS Expr2, dbo.AdEnroll.SyCampusID,
dbo.AdReason.Descrip AS Expr3,MIN(dbo.AdEnroll.StatusDate)
FROM dbo.syStudent INNER JOIN
dbo.AdEnroll ON dbo.syStudent.SyStudentId = dbo.AdEnroll.SyStudentID LEFT OUTER JOIN
dbo.AdReason ON dbo.AdEnroll.AdReasonID = dbo.AdReason.AdReasonID LEFT OUTER JOIN
dbo.SySchoolStatus ON dbo.AdEnroll.SySchoolStatusID = dbo.SySchoolStatus.SySchoolStatusID LEFT OUTER JOIN
dbo.AdProgramVersion ON dbo.AdEnroll.adProgramVersionID = dbo.AdProgramVersion.AdProgramVersionID
WHERE (dbo.AdEnroll.SyCampusID = 5) AND (dbo.SySchoolStatus.Code = 'drop' OR
dbo.SySchoolStatus.Code = 'inc') AND (NOT (dbo.AdProgramVersion.Code = 'teachcert'))

GROUP BY dbo.SySchoolStatus.Code, dbo.syStudent.SSN, dbo.syStudent.LastName,dbo.syStudent.FirstName,
dob.AdEnroll.CreditsEarned

I can't get this to run and I'm not sure why. What I need is to return only the MINIMUM date for dbo.AdEnroll.StatusDate. This represents the First course that the student took.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-01 : 11:51:50

when you're applying GROUP BY on certain fields, you cant use any other fields in select list unless you use any aggregate function. thats why i think above query errors.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-11-01 : 12:27:32

Isn't this: MIN(dbo.AdEnroll.StatusDate)an Aggregate function?

I tried to make it a sub query:

(SELECT
Min(dbo.AdEnroll.SatusDate)
From
dbo.AdEnroll group by LastName)

This won't work either.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-01 : 12:37:00

quote:
Originally posted by meberg66219

Isn't this: MIN(dbo.AdEnroll.StatusDate)an Aggregate function?

I tried to make it a sub query:

(SELECT
Min(dbo.AdEnroll.SatusDate)
From
dbo.AdEnroll group by LastName)

This won't work either.

yep
but you have whole lot of other columns in select which are not in group by like

dbo.SySchoolStatus.Descrip, dbo.AdProgramVersion.Code AS Expr1, dbo.AdProgramVersion.Descrip AS Expr2, dbo.AdEnroll.SyCampusID

hence its erroring

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-01 : 12:39:30

may be this is what you want


SELECT TOP (100) PERCENT dbo.syStudent.SSN, dbo.syStudent.LastName, dbo.syStudent.FirstName, dbo.AdEnroll.CreditsEarned, dbo.SySchoolStatus.Code, 
dbo.SySchoolStatus.Descrip, dbo.AdProgramVersion.Code AS Expr1, dbo.AdProgramVersion.Descrip AS Expr2, dbo.AdEnroll.SyCampusID, 
dbo.AdReason.Descrip AS Expr3,MIN(dbo.AdEnroll.StatusDate)
FROM dbo.syStudent INNER JOIN
dbo.AdEnroll ON dbo.syStudent.SyStudentId = dbo.AdEnroll.SyStudentID
INNERJOIN (SELECT LastName,Min(dbo.AdEnroll.SatusDate) AS MinDate
From
dbo.AdEnroll group by LastName) ad1
ON ad1.LastName = dbo.AdEnroll.LastName 
AND ad1.MinDate = dbo.AdEnroll.SatusDate
 LEFT OUTER JOIN
dbo.AdReason ON dbo.AdEnroll.AdReasonID = dbo.AdReason.AdReasonID LEFT OUTER JOIN
dbo.SySchoolStatus ON dbo.AdEnroll.SySchoolStatusID = dbo.SySchoolStatus.SySchoolStatusID LEFT OUTER JOIN
dbo.AdProgramVersion ON dbo.AdEnroll.adProgramVersionID = dbo.AdProgramVersion.AdProgramVersionID
WHERE (dbo.AdEnroll.SyCampusID = 5) AND (dbo.SySchoolStatus.Code = 'drop' OR
dbo.SySchoolStatus.Code = 'inc') AND (NOT (dbo.AdProgramVersion.Code = 'teachcert'))

GROUP BY dbo.SySchoolStatus.Code, dbo.syStudent.SSN, dbo.syStudent.LastName,dbo.syStudent.FirstName,
dob.AdEnroll.CreditsEarned

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-11-01 : 12:53:35

In the above Inner Join what does "ad1" represent?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-01 : 12:55:01

ad1 represents alias ie short name for derived table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-11-01 : 13:03:46

What is ad1 an alias for, which column?

In this example: Min(dbo.AdEnroll.StatusDate) AS MinDate
so StatusDate has been aliased as MinDate

But I don't see that ad1 was ever defined like MinDate has been.

Or at least from the errors that I got when trying to run the query, I assumed that might be the issue that ad1 was not defined as an alias. Because I got errors as to invalid column names for anything that came after the ad1 alias.

Thank you for the help. I have been working on this for a few days and I really am stuck so I appreciate the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-01 : 13:08:06

ad1 is alias for dervied table i.e below

(SELECT LastName,Min(dbo.AdEnroll.SatusDate) AS MinDate
From
dbo.AdEnroll group by LastName)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources