We help IT Professionals succeed at work.

mssql - group by & order by?

310 Views
Last Modified: 2012-05-07
I keep getting this error:
Incorrect syntax near the keyword 'ORDER'.

Basically, I'm trying to find all the rows grouped by email that have a count = 4.  I want those 4 to be ordered by the time field so that I can pull the entries in chronological order.  
insert into TableB
( qid, rep, time, email, idmarker )
( 
select TOP 2 qid, rep, time, email, idmarker
FROM TableA
WHERE email IN 
(
select email FROM 
tableA
group by email 
having COUNT(*) = 4
)
ORDER BY time 
)
GO

Open in new window

Comment
Watch Question

Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
you have a hanging (  The correct is below:

insert into TableB
( qid, rep, time, email, idmarker )
(
 select TOP 2 qid, rep, time, email, idmarker FROM TableA
WHERE email IN
       (select email FROM tableA group by email having COUNT(*) = 4)
)
ORDER BY time
)
GO
Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.