SQL Anywhere中的MAX子查询返回错误 [英] MAX Subquery in SQL Anywhere Returning Error

问题描述

在sqlanywhere 12中，我编写了以下查询，该查询返回了两行数据:

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

哪个返回:

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

我只想在"ORD_Timestamp"列中返回带有最大时间戳的行.在简单地尝试通过MAX("eDatabase"."OrderingInfo"."ORD_Timestamp")进行选择之后，我发现了许多帖子，描述了该方法如何不起作用以及如何使用子查询来获取结果.

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.

我无法以一种可行的方式创建子查询，并且在以下查询中，我在上一个"ON"上遇到语法错误:

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

有人知道我该如何调整它以使查询正确地仅选择最大ORD_Timestamp行?

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

推荐答案

尝试一下:

    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

这篇关于SQL Anywhere中的MAX子查询返回错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！