Equivalent of MAX in progressdb (openedge)

Question

I want to get only the top row for each user and in some cases there are 3. I've got it limited down to only a few rows if there are duplicates and unfortunately there is no further "data way" to limit the results.

A sample query is:

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

These people may have multiple employment records.

In MSSQL I would do a MAX() but I don't see that as an option in openedge? How can I limit each row to 1 of a unique IDNum?

You probably want to look into a way to do this with SQL 92 code, which is what Progress understands. If you're extracting that code from an ODBC, it's probably the best way out of this. Now if you want to do this with 4GL code, it's probably easier and cleaner for you, if it's an option. — bupereira, Apr 26 '17 at 20:34

score 0 · Answer 1 · edited May 23 '17 at 12:02

0

If you are using the SQL-92 engine (via an ODBC/JDBC connection) then you probably want to be using TOP:

Getting first 100 records from the table in Progress OpenEdge database (e.g. SELECT TOP 100..)

If you are using the 4Gl engine that is also covered in the answer above ;)

edited May 23 '17 at 12:02

Community

1
1

answered Apr 26 '17 at 23:08

Tom Bascom

13,405
2
27
33

Top will just give me X amount of rows, I need all rows just ensuring I have one of each record with a unique ID#. – Zonus Apr 27 '17 at 01:37

score 0 · Answer 2 · answered Apr 27 '17 at 07:35

0

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

answered Apr 27 '17 at 07:35

ForEachInvoiceDelete

1

distinct will not work in this scenario... there's more columns that make it non-distinct. – Zonus Apr 29 '17 at 00:38

score 0 · Answer 3 · answered Apr 11 '18 at 09:34

SQL-92 supports the MAX function:

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"

Equivalent of MAX in progressdb (openedge)

3 Answers3