order by in subselect

[Former Member]

Hello,

when using a ORDER BY clause in a subquery, i get the following error:

-

---

Error -

---

Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed

General error;-5016 POS(286) Missing delimiter: )

SELECT *

FROM DOCPROTOCOL D

WHERE D.DOCID = (

SELECT DP.DOCID

FROM DOCPROTOCOL DP

WHERE DP.DOCID = 5

AND DP.ACTIONSYM=10

ORDER BY DP.XTIME ASC

LIMIT 1

)

The "ORDER"-Keyword is marked.

Trying the same request without the order-by part works fine, but naturally returns something different - which is not the result I want.

(I know, that the above sample is kind of useless - the "real" statement is more complex, so I tried to reduce it to a minimum.)

Are ORDER BY's not supported in subqueries? If so, are there any alternatives? Or did I overlook something?

Thanks in advance.

[lbreddemann · Accepted solution]

>

> Hello,

>

> when using a ORDER BY clause in a subquery, i get the following error:

>

> -

---

Error -

---

> Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed

> General error;-5016 POS(286) Missing delimiter: )

> SELECT *

> FROM DOCPROTOCOL D

> WHERE D.DOCID = (

> SELECT DP.DOCID

> FROM DOCPROTOCOL DP

> WHERE DP.DOCID = 5

> AND DP.ACTIONSYM=10

> ORDER BY DP.XTIME ASC

> LIMIT 1

> )

>

> The "ORDER"-Keyword is marked.

> Trying the same request without the order-by part works fine, but naturally returns something different - which is not the result I want.

> (I know, that the above sample is kind of useless - the "real" statement is more complex, so I tried to reduce it to a minimum.)

>

> Are ORDER BY's not supported in subqueries? If so, are there any alternatives? Or did I overlook something?

No ORDER BY in subqueries aren't supported.

From what I see in your example something like this should work even better:

SELECT * 
     FROM DOCPROTOCOL D 
     WHERE D.DOCID = (
                              SELECT max(DP.DOCID)
                              FROM DOCPROTOCOL  DP
                              WHERE DP.DOCID = 5
                              AND DP.ACTIONSYM=10 
                       )

This is better because:

- it makes clear what you want to do

- it prevents useless sort operations

- it enables max/min optimizations if the DOCID is a indexed field or key column

regards,

Lars

[Former Member]

Great, thanks, that solves my problem.