sql server 2005 query syntax error

Question

I have the following query and it gives me an error :"Incorrect syntax near the keyword 'GROUP'." <-shows me the second GROUP BY statement...Can you tell me why?

SELECTventId
,TargetUser
,TargetDomain
,String05
,Amount
,max(Amount) maximum 
FROM
   (SELECT    EventId
              , TargetUser
              , TargetDomain
             , String05 
FROM  AdtServer.dvAll 
GROUP BY EventId, TargetUser, TargetDomain, String05)

GROUP BY EventId,TargetUser,TargetDomain,String05,Amount //ERROR SHOWS HERE

ORDER BY maximum,TargetUser

If I create view for inner query it works fine(But I don't want to create a view because if my query is long-running query it gives me time-out).

cjk · Accepted Answer · 2010-11-08 13:48:24Z

1

You need to give an alias to your table:

SELECTventId 
,TargetUser 
,TargetDomain 
,String05 
,Amount 
,max(Amount) maximum  
FROM 
   (SELECT    EventId 
              , TargetUser 
              , TargetDomain 
             , String05  
    FROM  AdtServer.dvAll  
    GROUP BY EventId, TargetUser, TargetDomain, String05
    ) x
GROUP BY EventId,TargetUser,TargetDomain,String05,Amount 
ORDER BY maximum,TargetUser

answered Nov 8, 2010 at 13:48

cjk

46.6k9 gold badges83 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kupa Over a year ago

You are right I found the answer but because of you are absolutely right I will give you accept..Thank you very much..:)

kupa · Accepted Answer · 2010-11-08 13:49:30Z

1

I found the answeeeeeeeer

I should have written as tempTB I mean my query should look like this:

SELECTventId
,TargetUser
,TargetDomain
,String05
,Amount
,max(Amount) maximum 
FROM
   (SELECT    EventId
              , TargetUser
              , TargetDomain
             , String05 
FROM  AdtServer.dvAll 
GROUP BY EventId, TargetUser, TargetDomain, String05) as tempTB
GROUP BY EventId,TargetUser,TargetDomain,String05,Amount 

ORDER BY maximum,TargetUser

answered Nov 8, 2010 at 13:49

kupa

1,9005 gold badges25 silver badges41 bronze badges

Collectives™ on Stack Overflow

sql server 2005 query syntax error

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related