Nested query error in sql server 2008

Question

I get the following error

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ')'.

when I try to run the following query

SELECT COUNT(*)
  FROM tbl_usgGroupXref 
 WHERE GroupID = 478 
   AND accountNo IN (
           SELECT TOP 5000 accountNo
             FROM (
               SELECT DISTINCT accountNo 
                 FROM tbl_usgGroupXref 
                WHERE GroupID = 478
              )
       )

Currently, my query consists of a series of nested queries from a single table tbl_usgGroupXref. The table consists of multiple months of records for each account. The innermost query is to get the distinct accounts. The next outer query is to get any 5000 of those accounts (I couldn't seem to combine TOP with DISTINCT). And the outermost query is to get the count of the actual records represented by the 5000 accounts.

Could anyone shed some light on the error or revise this sql so that it works?

I believe you can get rid of the innermost query with SELECT DISTINCT TOP 5000 accountNo — bfavaretto
– bfavaretto, Commented Dec 27, 2011 at 13:52

Yuck · Accepted Answer · 2011-12-27 13:48:55Z

3

You have to alias this subquery:

 FROM (
   SELECT DISTINCT accountNo 
     FROM tbl_usgGroupXref 
    WHERE GroupID = 478
  ) AS MySubQuery

Also to note, just saying TOP 5000 isn't any guarantee that you'll get the first records.

answered Dec 27, 2011 at 13:48

Yuck

51.1k13 gold badges108 silver badges136 bronze badges

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

Comments

gbn · Accepted Answer · 2011-12-27 14:08:50Z

3

You don't need the subquery to be so complex because the IN will ignore duplicates. DISTINCT isn't needed.

SELECT COUNT(*)
  FROM tbl_usgGroupXref 
 WHERE GroupID = 478 
   AND accountNo IN (
           SELECT TOP 5000 accountNo
             FROM tbl_usgGroupXref 
                WHERE GroupID = 478
       )

However, the TOP 5000 is pointless because you have no ORDER BY so you have 5000 arbitrary rows.

edited Dec 27, 2011 at 14:08

answered Dec 27, 2011 at 14:02

gbn

434k84 gold badges602 silver badges690 bronze badges

1 Comment

stevenl Over a year ago

Sorry, 'first' was imprecise - I was just looking for a random 5000. But note taken.

Oleg Dok · Accepted Answer · 2011-12-27 13:58:12Z

2

You have to assign an alias to most inner select as:

SELECT COUNT(*)
  FROM tbl_usgGroupXref 
 WHERE GroupID = 478 
   AND accountNo IN (
           SELECT TOP 5000 accountNo
             FROM (
               SELECT DISTINCT accountNo 
                 FROM tbl_usgGroupXref 
                WHERE GroupID = 478
              ) ALIAS
       )

OR

combine TOP and DISTINCT

SELECT COUNT(*)
  FROM tbl_usgGroupXref 
 WHERE GroupID = 478 
   AND accountNo IN (
           SELECT DISTINCT TOP 5000 accountNo
           FROM tbl_usgGroupXref 
           WHERE GroupID = 478
       )

edited Dec 27, 2011 at 13:58

answered Dec 27, 2011 at 13:49

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

Comments

ypercubeᵀᴹ · Accepted Answer · 2011-12-27 13:59:36Z

1

You could also write:

       SELECT TOP 5000 accountNo
         FROM (
           SELECT DISTINCT accountNo 
             FROM tbl_usgGroupXref 
            WHERE GroupID = 478
          )

as:

       SELECT TOP 5000 accountNo
       FROM tbl_usgGroupXref 
       WHERE GroupID = 478
       GROUP BY accountNo

and your whole query as:

SELECT SUM(cnt)
FROM 
  ( SELECT TOP 5000 
          COUNT(*) AS cnt
    FROM tbl_usgGroupXref 
    WHERE GroupID = 478
    GROUP BY accountNo
   ) grp

edited Dec 27, 2011 at 13:59

answered Dec 27, 2011 at 13:52

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Collectives™ on Stack Overflow

Nested query error in sql server 2008

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related