[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Simple Nested Select Problem

Posted on 2009-02-10
4
Medium Priority
?
304 Views
Last Modified: 2012-05-06
My brain is fried, what am I doing wrong in this simple nested select query

I get the error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.


I simply trying to find the largest STATE_ID value between two tables
select max(STATE_ID) AS STATE_ID
from	(
	select max(STATE_ID) AS STATE_ID from dbo.a270
	UNION ALL
	select max(STATE_ID) AS STATE_ID from dbo.d270
	)

Open in new window

0
Comment
Question by:dban00b
  • 2
4 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 1000 total points
ID: 23603253
check this
select max(STATE_ID) AS STATE_ID
from	(
	select max(STATE_ID) AS STATE_ID from dbo.a270
	UNION ALL
	select max(STATE_ID) AS STATE_ID from dbo.d270
	)t1

Open in new window

0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 1000 total points
ID: 23603288
You have to supply an alias to your derived table
select max(STATE_ID) AS STATE_ID
from    (
        select max(STATE_ID) AS STATE_ID from dbo.a270
        UNION ALL
        select max(STATE_ID) AS STATE_ID from dbo.d270
        ) a

Open in new window

0
 
LVL 1

Author Comment

by:dban00b
ID: 23603429
Doh--  I knew it was something simple like that, thanks guys. I'll split it up sharath was first, brandon explained my problem
0
 
LVL 41

Expert Comment

by:Sharath
ID: 23603497
No problem :)
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

873 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