5

I tried to use a proposed query on Sybase ASE 12, and it complained about syntax error.

SELECT 
    item, 
    ( SELECT TOP 1 tags.tag
      FROM #tags tags
        LEFT JOIN t o
          ON  tags.tag = o.tag
          AND o.item_id = n.item_id 
      WHERE o.tag IS NULL
      ORDER BY tags.tag
    ) 'tag',
    value  
FROM
    t_new n

ERROR: Incorrect syntax near the keyword 'top'.

However, the same query worked when I replaced (TOP 1 tag... ORDER BY tag) with MAX():

SELECT 
    item, 
    ( SELECT max(tags.tag)
      FROM #tags tags
        LEFT JOIN t o
          ON  tags.tag = o.tag
          AND o.item_id = n.item_id 
      WHERE o.tag IS NULL
        --  ORDER BY tags.tag
    ) 'tag',
    value  
FROM
    t_new n

  • Why is using (TOP 1 tag... ORDER BY tag) a problem in Sybase's correlated sub queries?

  • Is there any fix to the original query that does NOT use min()/max()?

Community
  • 1
  • 1
DVK
  • 126,886
  • 32
  • 213
  • 327
  • 1
    In Sybase, 'top' and 'order by' are mutually exclusive. The order for top is determined by clustered indexes. If there aren't clustered indexes, then the results can be unpredictable. Also, I didn't think top was supported until ASE 12.5.3, but I could be wrong. – Mike Gardner Feb 14 '13 at 14:55
  • Also: "Adaptive Server Enterprise version 12.5.3 supports the top n clause in outer query select statements, but not in the select list of a subquery. This differs from Microsoft SQL Server. Any attempt to use the top n clause with Adaptive Server in a subquery yields a syntax error." From the ASE 15.0.3 documentation here http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00641.1502/html/nfg1502/CHEHFFJJ.htm – Mike Gardner Feb 14 '13 at 14:59
  • @MichaelGardner - I think you're wrong on the first comment. Second comment should be an answer if you include the Sybase docs quote. – DVK Feb 14 '13 at 15:28
  • Yes, I was mistaken, ordering is only disallowed on updates/deletes. – Mike Gardner Feb 15 '13 at 14:31

2 Answers2

5

Adaptive Server Enterprise version 12.5.3 supports the top n clause in outer query select statements, but not in the select list of a subquery. This differs from Microsoft SQL Server. Any attempt to use the top n clause with Adaptive Server in a subquery yields a syntax error.

From the ASE 12.5.3 documentation here

Mike Gardner
  • 6,611
  • 5
  • 24
  • 34
0

I know it is late but just for others TOP inside a subquery can be used in sybase latest version.

Mrunal Gosar
  • 4,595
  • 13
  • 48
  • 71