Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

mssql - group by & order by?

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

0
n00b0101
Asked:
n00b0101
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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

0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now