select MAX and WHERE - 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

SQL Server 2000 Forums

Transact-SQL (2000)

select MAX and WHERE

Author

Topic

JoshBeagley
Starting Member

5 Posts

Posted - 2005-04-27 : 17:08:28

In my table I have a list of evaluations written on students by their instructors. If I'm the instructor, I want to see the last evaluation I wrote on my student.

So I need my statement to do two things:

1. Find every evaluation I've ever written (I'm the instructor)
2. From that group, find the only the latest evaluations for each student.

These are the fields I have for now: TEvals.date, TEvals.student, TEvals.instructor, TEvals.position, TEvals.daysauthorized

I think I'm just having trouble mixing my MAX/WHERE/GROUP BY statements together. Any help would be much appreciated. thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-27 : 17:23:43

This outta do it:


Select	[date]
	,student
	,instructor
	,position
	,daysauthorized
From	TEvals a
JOIN	(--derived table of latest evaluation by student
	Select	instructor
		,student
		,max([date]) [date]
	from	TEvals
	Where	instructor = 'You'
	Group by instructor
		,student
	) b
		ON a.instructor = b.instructor
		AND a.student = b.Student
		AND a.[date] = b.[date]

Be One with the Optimizer
TG

JoshBeagley
Starting Member

5 Posts

Posted - 2005-04-27 : 17:37:27

"Syntax error in FROM clause."

It then highlights JOIN

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-27 : 18:26:49

Are you using Microsoft Sql Server?

I WAS missing table aliases for select list, but no syntax errors:


Select	a.[date]
	,a.student
	,a.instructor
	,a.position
	,a.daysauthorized

Be One with the Optimizer
TG

JoshBeagley
Starting Member

5 Posts

Posted - 2005-04-27 : 19:46:02

I've talked myself into doing it another way, but yes I'm using the SQL view in MSAccess to test it, then I'm pulling it with asp on the company network.

Instead of pulling the latest evals on all my students, I'm going to pull a list of my students only. Then you click on that student to see his/her evaluations. It's the same effect, just one extra step.

thanks for the help!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-27 : 20:00:07

For future reference, this forum is specifically for MS Sql Server. There is a different forum on this site dedicated to MS Access.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources