SQL error on ORDER BY in subquery (TOP is used)

Question

I am getting Syntax error near 'ORDER' from the following query:

SELECT i.ItemID, i.Description, v.VendorItemID
FROM Items i 
JOIN ItemVendors v ON
    v.RecordID = (
                 SELECT TOP 1 RecordID
                 FROM ItemVendors iv
                 WHERE
                     iv.VendorID = i.VendorID AND
                     iv.ParentRecordID = i.RecordID
                 ORDER BY RecordID DESC
                 );

If I remove the ORDER BY clause the query runs fine, but unfortunately it is essential to pull from a descending list rather than ascending. All the answers I have found relating to this indicate that TOP must be used, but in this case I am already using it. I don't have any problems with TOP and ORDER BY when not part of a subquery. Any ideas?

Using `max()` causes the same error, but that is expected because using `order by` in a subquery requires the use of `top`. — ce_nort, Jan 22 '16 at 19:50
You probably meant for me to not use `order by` with your suggestion and I misunderstood. You were right, thanks! — ce_nort, Jan 22 '16 at 20:33

score 3 · Answer 1 · answered Jan 23 '16 at 13:09

3

This error has nothing to do with TOP. ASE simply does not allow ORDER BY in a subquery. That's the reason for the error.

answered Jan 23 '16 at 13:09

RobV

2,263
1
11
7

score 2 · Answer 2 · answered Jan 22 '16 at 18:58

2

RecordID in the ORDER BY is ambiguous. Add the appropriate table alias in front of it (e.g., iv.RecordID).

answered Jan 22 '16 at 18:58

Joe Stefanelli

132,803
19
237
235

1

If there is no ambiguity in select clause, why there is one in order by clause? Order by clause refers to the select list. I haven't sybase, have you ability to check it? – Hamlet Hakobyan Jan 22 '16 at 19:05
@HamletHakobyan Good eye! I missed that. It could very well be ambiguous in both locations. – Joe Stefanelli Jan 22 '16 at 19:09
I don't know how it works for sybase, but in case of sql server there is no ambiguity in that case. In correlated queries engine refers to subquery table (if it is one table) if nothing specified explicitly. – Hamlet Hakobyan Jan 22 '16 at 19:13
Unfortunately ambiguity is not the issue: using `iv.RecordID` does not work either, and using simply `RecordID` works for the select statement as long as order by is removed. – ce_nort Jan 22 '16 at 19:49

score 0 · Accepted Answer · answered Jan 22 '16 at 19:59

0

I'd use max instead of top 1 ... order by

SELECT i.ItemID, i.Description, v.VendorItemID FROM Items i JOIN ItemVendors v ON v.RecordID = ( SELECT max(RecordID) FROM ItemVendors iv WHERE iv.VendorID = i.VendorID AND iv.ParentRecordID = i.RecordID);

answered Jan 22 '16 at 19:59

Ben

485
9
19

Thank you! While this doesn't explain why I was getting the error I was, it does solve my problem! – ce_nort Jan 22 '16 at 20:04

SQL error on ORDER BY in subquery (TOP is used)

3 Answers3