MAX Subquery in SQL Anywhere Returning Error

Question

In sqlanywhere 12 I wrote the following query which returns two rows of data:

SELECT "eDatabase"."Vendor"."VEN_CompanyName", "eDatabase"."OrderingInfo"."ORD_Timestamp"
    FROM "eDatabase"."OrderingInfo" 
    JOIN "eDatabase"."Vendor" 
        ON "eDatabase"."OrderingInfo"."ORD_VEN_FK" = "eDatabase"."Vendor"."VEN_PK"
    WHERE ORD_INV_FK='7853' AND ORD_DefaultSupplier = 1

Which returns:

'**United Natural Foods IN','2018-02-07 15:05:15.513'
'Flora                    ','2018-02-07 14:40:07.491'

I would like to only return the row with the maximum timestamp in the column "ORD_Timestamp". After simply trying to select by MAX("eDatabase"."OrderingInfo"."ORD_Timestamp") I found a number of posts describing how that method doesn't work and to use a subquery to obtain the results.

I'm having difficulty creating the subquery in a way that works and with the following query I'm getting a syntax error on my last "ON":

SELECT "eDatabase"."Vendor"."VEN_CompanyName", "eDatabase"."OrderingInfo"."ORD_Timestamp"
FROM ( "eDatabase"."OrderingInfo"
JOIN 
    "eDatabase"."OrderingInfo" 
    ON "eDatabase"."Vendor"."VEN_PK" = "eDatabase"."OrderingInfo"."ORD_VEN_FK" )
INNER JOIN 
    (SELECT "eDatabase"."Vendor"."VEN_CompanyName", MAX("eDatabase"."OrderingInfo"."ORD_Timestamp") 
    FROM "eDatabase"."OrderingInfo") 
    ON "eDatabase"."Vendor"."VEN_PK" = "eDatabase"."OrderingInfo"."ORD_VEN_FK"
WHERE ORD_INV_FK='7853' AND ORD_DefaultSupplier = 1

Does anyone know how I can adjust this to make the query correctly select only the max ORD_Timestamp row?

Answer 1

try this:

    SELECT TOP 1 "eDatabase"."Vendor"."VEN_CompanyName", "eDatabase"."OrderingInfo"."ORD_Timestamp"
        FROM "eDatabase"."OrderingInfo" 
        JOIN "eDatabase"."Vendor" 
            ON "eDatabase"."OrderingInfo"."ORD_VEN_FK" = "eDatabase"."Vendor"."VEN_PK"
        WHERE ORD_INV_FK='7853' AND ORD_DefaultSupplier = 1
order by "ORD_Timestamp" desc

this orders them biggest on to and say only hsow the top row