Group By - 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 2008 Forums

Transact-SQL (2008)

Group By - HELP

Author

Topic

OON
Starting Member

22 Posts

Posted - 2010-09-20 : 15:46:48

Hello,

i have the following code -

SELECT distinct

r.referral_id,r.EXTERNAL_ID_NUM, nmr3.NOTE_SUMMARY AS LETTER, nmr3.contact_date

FROM

KPGA_ODS.CLARITY.REFERRAL AS r

inner JOIN
(SELECT nmr2.RECORD_ID, znt2.NAME, nmr2.note_user_id, hno2.NOTE_SUMMARY,max(hno2.contact_date)
FROM KPGA_ODS.CLARITY.NOTES_MC_RFL AS nmr2
INNER JOIN KPGA_ODS.CLARITY.HNO_ENC_INFO as hno2 on hno2.NOTE_ID = nmr2.NOTE_ID AND hno2.Note_Type_C= 7
INNER JOIN KPGA_ODS.CLARITY.ZC_NOTE_TYPE as znt2 on znt2.NOTE_TYPE_C = hno2.NOTE_TYPE_C) AS nmr3
ON r.referral_id = nmr3.RECORD_ID
where r.EXTERNAL_ID_NUM = ''
-----------------------------------------------

i want to use the max function on hno2.contact_date in the subquery. when i do this, where do i put the group by clause and what fields need to be in the group by?

i keep getting errors.

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-20 : 16:02:29

SELECT nmr2.RECORD_ID, znt2.NAME, nmr2.note_user_id, hno2.NOTE_SUMMARY,max(hno2.contact_date)
FROM KPGA_ODS.CLARITY.NOTES_MC_RFL AS nmr2
INNER JOIN KPGA_ODS.CLARITY.HNO_ENC_INFO as hno2 on hno2.NOTE_ID = nmr2.NOTE_ID AND hno2.Note_Type_C= 7
INNER JOIN KPGA_ODS.CLARITY.ZC_NOTE_TYPE as znt2 on znt2.NOTE_TYPE_C = hno2.NOTE_TYPE_C) AS nmr3
ON r.referral_id = nmr3.RECORD_ID
where r.EXTERNAL_ID_NUM = ''
GROUP BY nmr2.RECORD_ID, znt2.NAME, nmr2.note_user_id, hno2.NOTE_SUMMARY

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

OON
Starting Member

22 Posts

Posted - 2010-09-20 : 16:18:23

Thanks for your response.

Here's what i have and it didnt work...

SELECT distinct

r.referral_id,r.EXTERNAL_ID_NUM, nmr3.NOTE_SUMMARY AS LETTER, nmr3.contact_date,nmr3.RECORD_ID

FROM

KPGA_ODS.CLARITY.REFERRAL AS r

inner JOIN
(SELECT nmr2.RECORD_ID, znt2.NAME, nmr2.note_user_id, hno2.NOTE_SUMMARY,max(hno2.contact_date)
FROM KPGA_ODS.CLARITY.NOTES_MC_RFL AS nmr2
INNER JOIN KPGA_ODS.CLARITY.HNO_ENC_INFO as hno2 on hno2.NOTE_ID = nmr2.NOTE_ID AND hno2.Note_Type_C= 7
INNER JOIN KPGA_ODS.CLARITY.ZC_NOTE_TYPE as znt2 on znt2.NOTE_TYPE_C = hno2.NOTE_TYPE_C
) AS nmr3
ON r.referral_id = nmr3.RECORD_ID
where r.EXTERNAL_ID_NUM = '110339451'--'201187477'--'201187654'
GROUP BY nmr2.RECORD_ID, znt2.NAME, nmr2.note_user_id, hno2.NOTE_SUMMARY

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-20 : 18:44:57

no, the group by goes inside with your derived table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

OON
Starting Member

22 Posts

Posted - 2010-09-21 : 10:03:26

got it. thank you sir!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources