Max function not working or expression in SELECT list not valid

Question

The initial results that I got is this, but there are unnecessary rows that were included in the result set.

SELECT JWAN8 AS EMP_ID, JWDTAI AS DATA_ITEM, JWHSTD AS JULIAN_DATE, JWEFTO AS EFF_DATE, JWUPMJ AS DATE_UPDATED, JWTRS AS CHANGE_REASON
FROM PRODDTA.F08042
WHERE JWAN8 IN (146750, 1013535, 616248)
AND JWDTAI = 'DT'

My goal is to get the results in Row 2 and Row 9 only. What I did is by adding max(JWUPMJ) to the date_updated field.

SELECT JWAN8 AS EMP_ID, JWDTAI AS DATA_ITEM, JWHSTD AS JULIAN_DATE, JWEFTO AS EFF_DATE, max(JWUPMJ) AS DATE_UPDATED, JWTRS AS CHANGE_REASON
FROM PRODDTA.F08042
WHERE JWAN8 IN (146750, 1013535, 616248)
  AND JWDTAI = 'DT'

So what I did is I tried removing the comma to make the MAX function work.

SELECT JWAN8 AS EMP_ID, JWDTAI AS DATA_ITEM, JWHSTD AS JULIAN_DATE, JWEFTO AS EFF_DATE, MAX(REPLACE(JWUPMJ,',',' ')) AS DATE_UPDATED, JWTRS AS CHANGE_REASON
FROM PRODDTA.F08042
WHERE JWAN8 IN (146750, 1013535, 616248)
  AND JWDTAI = 'DT'

Still not working. Can someone help? I'm new into SQL

I have tried CAST(JWUPMJ AS INTEGER), but it didn't help to make the MAX function work.

Bohemian · Answer 1 · 2023-07-28T09:26:01.957

1

You need a GROUP BY clause that lists the non-aggregated selected expressions:

SELECT JWAN8 AS EMP_ID, JWDTAI AS DATA_ITEM, JWHSTD AS JULIAN_DATE, JWEFTO AS EFF_DATE, MAX(REPLACE(JWUPMJ,',',' ')) AS DATE_UPDATED, JWTRS AS CHANGE_REASON
FROM PRODDTA.F08042
WHERE JWAN8 IN (146750, 1013535, 616248)
  AND JWDTAI = 'DT'
GROUP BY JWAN8, JWDTAI, JWHSTD, JWEFTO, JWTRS

edited Jul 28 '23 at 09:26

answered Jul 28 '23 at 09:10

Bohemian

412,405
93
575
722

You need to modify your query and include EMP_ID into your GROUP BY, see https://i.stack.imgur.com/VU0st.jpg – Lajos Arpad Jul 28 '23 at 09:21
@LajosArpad no. EMP_ID is an alias for JWAN8 – Bohemian Jul 28 '23 at 09:24
You are correct. Edited my answer and upvoted yours. – Lajos Arpad Jul 28 '23 at 09:40

Lajos Arpad · Answer 2 · 2023-07-28T09:40:11.693

First, let's clarify the difference between record-level and aggregate values.

If you have a table t and it has a number of n records, and it has, among others, two columns, c1 and c2, then you can SELECT c1, c2 FROM t to get the record-level values of c1 and c2.

However, if you want to get their maximum, that's an aggregate value, that is, it's related to a group of items rather than to records. This is why, if you do a

SELECT MAX(c1), c2
FROM t

then you will receive an error, because you self-contradict when you want the aggregate of c1, via calling the MAX aggregator, whereas your c2 is a record-level field. In such cases, you either aggregate c2 as well, for example via

SELECT MAX(c1), AVG(c2)
FROM t

which will result with exactly a single line, that will contain the maximum of c1 and the average of c2; or GROUP BY c2, converting it into an aggregate for the context of the selection:

SELECT MAX(c1), c2
FROM t
GROUP BY c2

If you do this group by, then the number of records in your resulting relation will be the number of different c2 values and each record will contain the maximum c1 for that group and the c2 value.

In your case, since you want to have multiple records, you surely need to GROUP BY JWAN8, but you are also using other non-aggregate fields and you will need to either include them to your GROUP BY clause, like Bohemian suggested, or use an aggregate function. To determine what works best for you, you need to ask yourself the following question:

Are there distinct values for my further non-aggregate fields for my groups and if so, should I have separate records?

By answering this question, you will know what you need to do.

Max function not working or expression in SELECT list not valid

2 Answers2