Going from SQLite to SAP ASE/SQL Server need some assistance on query rewrite

Question

I wrote the following query in SQLite, which works fine, but have found out the office utilizes SAP ASE (Sybase SQL Server) and it does not display the same result there.

select
dm04_maf.mcn,
dm04_maf.wc_cd,
dm04_maf.buno_serno,
max(dm12_maf_note.maf_note) as Last_Note,
dm12_maf_note.note_dttm as Time_of_Note,
dm12_maf_note.orignr
from
dm04_maf
left join
dm12_maf_note on dm04_maf.mcn = dm12_maf_note.mcn
where dm04_maf.ty_maf_cd = 'TD'
group by dm04_maf.mcn

I believe it is not performing group by correctly as it isn't giving me the last note for each mcn (primary key) it is giving me every note for each mcn.

Any guidance for this would be appreciated.

Answer 1

An ANSI compliant group by query will have all non-aggregate columns (from the select/projection list) also in the group by clause. While many RDBMSs will allow non-ANSI compliant group by queries (like in this question), how each RDBMS chooses to process said non-ANSI compliant group by query is up for grabs (ie, there is no guarantee of getting the same result across different RDBMSs).

Some assumptions:

• OP mentions wanting to display just the 'last note'; for now we'll assume that max(maf_note) is sufficient to determine the 'last note' for a given mcn value
• the other non-aggregate columns (eg, wc_cd, buno_serno, note_dttm and orignr) should come from the same row that produces last note = max(maf_note)`

Since SAP (Sybase) ASE does not support windows functions nor ROW_NUMBER(), one idea would be to use a sub-query to find the 'last note' and then join this into the main query to pull the rest of the desired values, eg:

select  dm1.mcn,
        dm1.wc_cd,
        dm1.buno_serno,
        dt.Last_Note,
        dmn1.note_dttm as Time_of_Note,
        dmn1.orignr

from    dm04_maf      dm1
left
join    dm12_maf_note dmn1
on      dm1.mcn = dmn1.mcn

join   (select dm2.mcn,
               max(dmn2.maf_note) as Last_Note

        from   dm04_maf      dm2
        join   dm12_maf_note dmn2
        on     dm2.mcn = dmn2.mcn

        where  dm2.ty_maf_cd = 'TD'
        group by dm2.mcn
       ) dt

on      dm1.mcn       = dt.mcn
and     dmn1.maf_note = dt.Last_Note
where  dm1.ty_maf_cd = 'TD'

NOTES:

• the extra dm1.ty_maf_cd = 'TD' is likely redundant; will leave it up to the OP to decide on whether to keep or remove
• (obviously) may need to come back and tweak based on validity of the assumptions and/or updates to the question