SELECT Max() from subquery.

• Kurt Wimberger

  SSC Veteran

  Points: 236

  More actions

  September 22, 2016 at 4:00 pm

  #329968

  All: SQL Server 2012. I have a legacy database and can't change the schema. One table uses IDs that can be formatted like any of these:

  ##AA####AAA

  ##AA###AAA

  ##AA##AAA

  ##AA####

  ##AA###

  ##AA##

  Essentially I want to reduce all IDs down to the trailing numeric portion and then find the max value. The inner query works great ...

  SELECT

  'ID' =

  IIF

  (

  (IsNUMERIC(RIGHT(REPLACE([SID], LEFT(SId, 4), ''), 3)) = 1)

  , CAST(REPLACE([SID], LEFT(SId, 4), '') AS Integer)

  , CAST(REPLACE((REPLACE(SId, (LEFT(SId, 4)), '')), (RIGHT(SId, 3)), '') AS Integer)

  )

  FROM tblHN

  But as soon as I wrap that in an outer query to return the max val ...

  SELECT 'x' = MAX(ID)

  FROM

  (

  SELECT

  'ID' =

  IIF

  (

  (IsNUMERIC(RIGHT(REPLACE([SID], LEFT(SId, 4), ''), 3)) = 1)

  , CAST(REPLACE([SID], LEFT(SId, 4), '') AS Integer)

  , CAST(REPLACE((REPLACE(SId, (LEFT(SId, 4)), '')), (RIGHT(SId, 3)), '') AS Integer)

  )

  FROM tblHN

  )

  This returns: Msg 102, Level 15, State 1, Line 13 Incorrect syntax near ')'.

  I have tried several different ways to alias/not alias the MAX() column, but nothing gets me anything other than this error.

  Can anyone see what I'm missing? I have also Googled "T-SQL select max from sub query" but have found nothing useful.

  Thanks

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  September 22, 2016 at 4:08 pm

  #1902674

  At first glance you are missing an alias for the derived table - just add an alias after the last bracket

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]