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?

score 0 · Accepted Answer · answered Feb 10 '18 at 21:15

0

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

answered Feb 10 '18 at 21:15

Jon Cluff

109
7

Wow, thank you! I've been spending a while on this and will need to investigate how you achieved it. I wasn't aware of "TOP" at all, but this is a much simpler solution than what I've been encountering elsewhere. Thanks again! – Feb 10 '18 at 21:22
No problem, the only difference is the order by which sorts it, the DESC sorts big to little (DESCending Order) top 1 says only show 1 row, if you wanted the top 5 change the 1 to a 5 – Jon Cluff Feb 10 '18 at 21:32

MAX Subquery in SQL Anywhere Returning Error

1 Answers1

Linked