CODEWITHSUNDEEP
sqlsybase-asa
ce_nort
ce_nort

Reputation: 168

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

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?

Upvotes: 2

Views: 1021

Answers (3)

RobV
RobV

Reputation: 2378

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.

Upvotes: 3

Ben
Ben

Reputation: 489

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);

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

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

Upvotes: 2

Related Questions