Subquery syntax error

Question

I'm not entirely sure why this doesn't work. I'm sure its very basic SQL and I'm missing something somewhere.

SELECT COUNT(*)
FROM (SELECT MIN(sys.tables.name)
        FROM sys.tables)

I'm getting this error Message::

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

The correct output should be just one number, the number of rows in the first table in the current database.

How do you determine the first table in current database. One with minimum object id or based on alphabetic characters? — Deepshikha
– Deepshikha, Commented Nov 25, 2013 at 8:01

roman · Accepted Answer · 2013-11-25 07:52:13Z

2

To get your query working, just add alias after last ) and alias name for column:

SELECT COUNT(*)
FROM (SELECT MIN(sys.tables.name) as name FROM sys.tables) as A

But this one will not return you number of rows in the first table in the current database. It will just return 1, because there's one record in this subquery. To get number of rows in the first table in the current database you have to use dynamic SQL, something like :

declare @Table_Name nvarchar(128), @stmt nvarchar(max)

select @Table_Name = min(name) from sys.tables

select @stmt = 'select ''' + @Table_Name + ''' as Table_Name, count(*) from ' + @Table_Name

exec sp_executesql @stmt = @stmt

answered Nov 25, 2013 at 7:52

roman

118k30 gold badges205 silver badges209 bronze badges

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

2 Comments

Noam Rathaus Over a year ago

Yes any new tables you create MUST have an alias, or there is an SQL error

zenrumi Over a year ago

Thank you! I'll try that. As separate queries it worked, getting the first table and then the count for that table. I thought together would work also but I see my error in logic here! It will return an int value. XD.

Deepshikha · Accepted Answer · 2013-11-25 08:08:28Z

0

To get the number of rows in the first table in the current database you can write a query as:

 SELECT SUM(pa.rows) RowCnt
 FROM sys.tables ta
 INNER JOIN sys.partitions pa
 ON pa.OBJECT_ID = ta.OBJECT_ID
 WHERE ta.name =(SELECT MIN(T.name) FROM sys.tables T) 
 GROUP BY ta.name

answered Nov 25, 2013 at 8:08

Deepshikha

10.3k2 gold badges24 silver badges21 bronze badges

Collectives™ on Stack Overflow

Subquery syntax error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related