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屋!