Pervasive select max date selects the minimum date

Question

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?

question from:https://stackoverflow.com/questions/65942933/pervasive-select-max-date-selects-the-minimum-date

Answer 1

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.