Equivalent of MAX in progressdb (openedge)

Question

我只想获得每个用户的第一行，在某些情况下都有3个。没有进一步的"数据方式"来限制结果。

示例查询是：

SELECT
 "PUB"."EMPLOYEE"."FirstName",
 "PUB"."EMPLOYEE"."LastName",
 "PUB"."COMP"."IDNum"
FROM "PUB"."EMPLOYEE" 
JOIN "PUB"."COMP" on "PUB".EMPLOYEE"."ID" = "PUB"."COMP"."ID"
WHERE "PUB"."EMPLOYEE"."ENDEMPLOY" is null

这些人可能有多个就业记录。

在MSSQL中，我会做一个max((，但是我认为这是OpenEdge中的选择吗？如何将每行限制为唯一iDnum的1？

Answer 1

如果您使用的是SQL-92引擎(通过ODBC/JDBC连接(，那么您可能想使用TOP：

从" openEDGE"数据库中获取第一个100个记录(例如，选择顶部100 ..(

如果您使用的是上面答案也涵盖的4GL引擎；(

Answer 2

SELECT DISTINCT
 "PUB"."EMPLOYEE"."FirstName",
 "PUB"."EMPLOYEE"."LastName",
 "PUB"."COMP"."IDNum"
FROM "PUB"."EMPLOYEE" 
JOIN "PUB"."COMP" on "PUB".EMPLOYEE"."ID" = "PUB"."COMP"."ID"
WHERE "PUB"."EMPLOYEE"."ENDEMPLOY" is null

Answer 3

sql-92支持最大函数：

SELECT
 "PUB"."EMPLOYEE"."FirstName",
 "PUB"."EMPLOYEE"."LastName",
 MAX("PUB"."COMP"."IDNum") AS "IDNum"
FROM "PUB"."EMPLOYEE" 
  LEFT JOIN "PUB"."COMP" ON
    "PUB".EMPLOYEE"."ID" = "PUB"."COMP"."ID"
WHERE 
  "PUB"."EMPLOYEE"."ENDEMPLOY" IS NULL
GROUP BY
 "PUB"."EMPLOYEE"."FirstName",
 "PUB"."EMPLOYEE"."LastName"