need query help - 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)

need query help

Author

Topic

gotafly
Yak Posting Veteran

54 Posts

Posted - 2005-05-26 : 14:04:50

SELECT tblACRDIMProgramReview.ProgSumID, tblACRDIMProgramReview.SysOrgLastReviewDate, tblACRDIMProgram.ProgramName,
tblACRDIMProgramReview.ProgramSID
FROM tblACRDIMProgramReview INNER JOIN
tblACRDIMProgram ON tblACRDIMProgramReview.ProgramSID = tblACRDIMProgram.ProgramSID
WHERE (tblACRDIMProgramReview.ProgSumStatusID = 1) AND (tblACRDIMProgramReview.SysOrgSID = 2)

Will get.....

26 ///9/30/2001 ///Business Marketing ///67
27 ///9/30/2001 ///Adm & Mgmt (Assoc Degree) ///66
28 ///5/28/2004 ///Adm & Mgmt (Assoc Degree) ///66
29 ///12/12/2005 ///Adm & Mgmt (Assoc Degree) ///66

I want to onyy return the max SysOrgLastReviewDate for each ProgramSID
so.....

26 ///9/30/2001 ///Business Marketing ///67
29 ///12/12/2005 ///Adm & Mgmt (Assoc Degree) ///66

jhermiz

3564 Posts

Posted - 2005-05-26 : 14:14:55

so use max and proper group by ?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

gotafly
Yak Posting Veteran

54 Posts

Posted - 2005-05-26 : 14:19:05

I only know how to do this, but I loose the other fields..

SELECT tblACRDIMProgramReview.ProgramSID, MAX(tblACRDIMProgramReview.SysOrgLastReviewDate)
FROM tblACRDIMProgramReview INNER JOIN
tblACRDIMProgram ON tblACRDIMProgramReview.ProgramSID = tblACRDIMProgram.ProgramSID
WHERE (tblACRDIMProgramReview.ProgSumStatusID = 1) AND (tblACRDIMProgramReview.SysOrgSID = 2)
GROUP BY tblACRDIMProgramReview.ProgramSID

jhermiz

3564 Posts

Posted - 2005-05-26 : 14:34:30

Do you know the differences between GROUP BY and DISTINCT? If you have duplicates and you use DISTINCT you will need to use Aggregate functions (Max(), etc) to display your data correctly.

Remember DISTINCT works on the entire row and not a single column...so if *you* think the row is distinct just because you have put the keyword DISTINCT in the beginning and you're basing it on that column think again.

Use DISTINCT and play with the aggregates and you should be able to get it.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources