Solved

mssql - group by & order by?

Posted on 2009-07-01
2
240 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

0
Comment
Question by:n00b0101
2 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 24755794
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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24755807
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now