Can someone tell me why this doesn't work?
SELECT MAX(LNK_DATE), LNK_LINK
FROM ISLINKS
WHERE "ORDERNUM" = 140420
GROUP BY LNK_LINK
This returns the lowest date value for 2 records. I'm looking for the max date. What am I missing?
I'm using PSQL v11 but I'm sure it's the same across most versions.
Using the following SQL, I get the correct (max date) results when the LNK_LINK field is the same. If it is different between the two records, the engine will treat it as different records and display them when the GROUP BY is used. Here's the SQL I used:
create table SO_ISLINKS (lnk_Date date, ORDERNUM int, lnk_link char(100))#
insert into SO_ISLINKS values ('2021-01-28', 140420, 'link1')#
insert into SO_ISLINKS values ('2021-01-15', 140420, 'link1')#
insert into SO_ISLINKS values ('2021-01-25', 140420, 'link2')#
insert into SO_ISLINKS values ('2021-01-23', 140420, 'link3')#
insert into SO_ISLINKS values ('2021-01-01', 140421, 'link4')#
SELECT MAX(LNK_DATE), LNK_LINK
FROM SO_ISLINKS
WHERE "ORDERNUM" = 140420
GROUP BY LNK_LINK
This returns:
EXPR_1 lnk_link
========== ==========
1/28/2021 link1
1/25/2021 link2
1/23/2021 link3
When LNK_LINK is the same (link1) in my query, it shows only the one Date (1/28/2021).
If you take the LNK_LINK out of the query, it should show the Max Date across all records.