Datetime - 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

Datetime

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-09-27 : 09:40:55

Ok,

I am trying to get the maximum datetime and display results but I still receive all the records and NOT the record with maximum date. What am I doing wrong ?

SELECT     gprdsql.TblPracDetails.prac_no, 
           gprdsql.TblPracDetails.practice_name,
		   Max(gprdsql.TblContact.contact_change) AS Max_date
FROM       gprdsql.TblPracDetails INNER JOIN
           gprdsql.TblContact ON gprdsql.TblPracDetails.prac_no = gprdsql.TblContact.prac_no
WHERE      (gprdsql.TblPracDetails.prac_status = 'active') AND (gprdsql.TblContact.contact_type = 'collections')
group by    gprdsql.TblPracDetails.prac_no, gprdsql.TblPracDetails.practice_name, gprdsql.TblContact.contact_change
Order by    gprdsql.TblPracDetails.prac_no, gprdsql.TblContact.contact_change

Results:

10 London Hospital NULL
10 London Hospital 2009-01-21 13:02:10.000
10 London Hospital 2010-05-10 12:23:43.000
16 WestMind Medical Centre NULL
16 WestMind Medical Centre 2009-01-21 13:02:30.000
16 WestMind Medical Centre 2009-06-01 10:24:15.000

Required Results:

10 London Hospital 2010-05-10 12:23:43.000
16 WestMind Medical Centre 2009-06-01 10:24:15.000

Many Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-27 : 10:16:37

[code]
group by gprdsql.TblPracDetails.prac_no, gprdsql.TblPracDetails.practice_name, ~~gprdsql.TblContact.contact_change~~
[/code]

KH
[spoiler]Time is always against us[/spoiler]

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-09-27 : 10:20:14

Received the error:

Msg 8127, Level 16, State 1, Line 1
Column "gprdsql.TblContact.contact_change" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-27 : 10:21:29

[code]
group by gprdsql.TblPracDetails.prac_no, gprdsql.TblPracDetails.practice_name, ~~gprdsql.TblContact.contact_change~~
Order by gprdsql.TblPracDetails.prac_no, ~~gprdsql.TblContact.contact_change~~ Max_date
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources