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

max

Author

Topic

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2008-02-04 : 23:35:03

using the following code gives me the results i want. but if i were to add the notes field in the select eg a_1.notes. the max function no longer works. Please help

SELECT a_1.fullname, MAX(a_1.createdon) AS Date
FROM FilteredAnnotation AS a INNER JOIN
(SELECT MAX(filteredannotation.createdon) as mel,FilteredAnnotation.createdon, FilteredContact.fullname, FilteredAnnotation.objectid, FilteredAnnotation.objecttypecode,
CAST(FilteredAnnotation.notetext AS VARCHAR(1024)) AS NOTES
FROM FilteredAnnotation INNER JOIN
FilteredContact ON FilteredContact.contactid = FilteredAnnotation.objectid
WHERE (FilteredContact.new_ratingname IN ('cold', 'cool'))
GROUP BY FilteredAnnotation.createdon, FilteredContact.fullname, FilteredAnnotation.objectid, FilteredAnnotation.objecttypecode
,CAST(FilteredAnnotation.notetext AS VARCHAR(1024))
) AS a_1 ON a.objectid = a_1.objectid
WHERE (a_1.objecttypecode = '2')
GROUP BY a_1.fullname

Compnetsyslc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-04 : 23:41:27

you should either use MAX() or MIN() aggregate functions or add the notes field too to GROUP BY according to your requirement. For complete solurtion,please post your requirement with sample data,reqd. o/p and table structures

SELECT a_1.fullname,MAX(a_1.NOTES),MAX(a_1.createdon) AS Date
FROM FilteredAnnotation AS a INNER JOIN
(SELECT MAX(filteredannotation.createdon) as mel,FilteredAnnotation.createdon, FilteredContact.fullname, FilteredAnnotation.objectid, FilteredAnnotation.objecttypecode, 
CAST(FilteredAnnotation.notetext AS VARCHAR(1024)) AS NOTES
FROM FilteredAnnotation INNER JOIN
FilteredContact ON FilteredContact.contactid = FilteredAnnotation.objectid
WHERE (FilteredContact.new_ratingname IN ('cold', 'cool'))
GROUP BY FilteredAnnotation.createdon, FilteredContact.fullname, FilteredAnnotation.objectid, FilteredAnnotation.objecttypecode
,CAST(FilteredAnnotation.notetext AS VARCHAR(1024)) 
) AS a_1 ON a.objectid = a_1.objectid
WHERE (a_1.objecttypecode = '2')
GROUP BY a_1.fullname

SELECT a_1.fullname,a_1.NOTES
,MAX(a_1.NOTES) MAX(a_1.createdon) AS Date
FROM FilteredAnnotation AS a INNER JOIN
(SELECT MAX(filteredannotation.createdon) as mel,FilteredAnnotation.createdon, FilteredContact.fullname, FilteredAnnotation.objectid, FilteredAnnotation.objecttypecode, 
CAST(FilteredAnnotation.notetext AS VARCHAR(1024)) AS NOTES
FROM FilteredAnnotation INNER JOIN
FilteredContact ON FilteredContact.contactid = FilteredAnnotation.objectid
WHERE (FilteredContact.new_ratingname IN ('cold', 'cool'))
GROUP BY FilteredAnnotation.createdon, FilteredContact.fullname, FilteredAnnotation.objectid, FilteredAnnotation.objecttypecode
,CAST(FilteredAnnotation.notetext AS VARCHAR(1024)) 
) AS a_1 ON a.objectid = a_1.objectid
WHERE (a_1.objecttypecode = '2')
GROUP BY a_1.fullname,a_1.NOTES

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2008-02-04 : 23:48:36

Thank you. Well Done. The first Example works fine. Wow, i just wasted 4 hours trying to figure it out.

Thanks a million

Compnetsyslc

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2008-02-05 : 00:42:00

Okay one more question? How do i configure this code do an reverse of what it does now. More or less give me all the results it would have left our running this query.

Compnetsyslc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 00:48:15

For giving solution to that i need to understand what you have done currently. So can you explain your table structures and explain your requirement in deatil with sample data and expected o/p?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources