CODEWITHSUNDEEP
sqlsql-server
BuddyCool
BuddyCool

Reputation: 91

Use of group by in Subquery

I have this following piece of line when executed, gives me this error. How would I use exists here for the filter line 'next_NotedID' ? Thanks in Advance

 Msg 116, Level 16, State 1, Line 27
    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

this is my where clause 

 where (Name = 'File Review' and next_Name = 'File Review')
                                                 and   (TYPECODE ='1200005' and next_Typecode = '1200005')
                                                 and   ((HEADLINE like '%Follow up letter%')
                                                        and (next_Headline like '%Follow up letter%'))
                                                  and next_NotedID <>  (SELECT  ParentRef, max(NoteID)from Stg.IG_Note group by PARENTREF)

Upvotes: 0

Views: 36

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can make your subquery as correlated :

. . .
and next_NotedID <> (SELECT max(nt.NoteID) 
                     from Stg.IG_Note nt 
                     where nt.PARENTREF = outerqueryalise.ParentRef
                     );

If you are comparing with next_NotedID with inner query max(NoteID) then you can directly express it as 

and next_NotedID <> (select max(nt.NoteID) from Stg.IG_Note nt);

Upvotes: 1

Brad
Brad

Reputation: 3591

change this line:

and next_NotedID <>  (SELECT  ParentRef, max(NoteID)from Stg.IG_Note group by PARENTREF)

to:

and next_NotedID <>  (SELECT  max(NoteID)from Stg.IG_Note group by PARENTREF)

You can not select more then 1 field in a sub select when using <>. If this returns more then one possible value you would need to change it to NOT IN instead of <>

Upvotes: 0

Related Questions