How to Display Values Excluding Maximum Value in Informix

informix

I am facing a challenging issue where I need to print all fields from a specfic column based on a duplicate column that excludes the maximum value

My query plan:

select event_no,
       bs_id
from   bs_bank_mast
where  event_no in (select   event_no 
                    from     bs_bank_mast 
                    group by event_no 
                    having   count(*) > 1) 
order by event_no ASC

My output:

event_no       bs_id

1692163        40672
1692163        41974
1692163        40672
1692163        41974
1723264        67460
1723264        67499
1723264        68357
1723265        67929
1723265        67383
1723266        67735
1723266        67423
1723266        67969
1723266        68164
1723266        67501 


event_no       bs_id

1692163        40672
1723264        67460
1723264        67499
1723265        67929
1723266        67735
1723266        67423
1723266        67969
1723266        68164

So basically everything but the last max value from bs_id is printed

The query I tried:

select event_no,
       bs_id
from   bs_bank_mast
where  event_no in (select   event_no 
                    from     bs_bank_mast 
                    group by event_no 
                    having   count(*) > 1) 
and    event_no < max(bs_id)

The error I get is:

Bad use of aggregate in this context

Please will you provide the assistance for what I need.

Best Answer

You cannot use an aggregated on WHERE clause. Instead, you could join your table with your grouped result set and discard maximum value.

select t1.event_no,
       t1.bs_id
from   bs_bank_mast t1
join   (select   event_no, max(bs_id) max_bs_id
        from     bs_bank_mast 
        group by event_no 
        having   count(*) > 1) t2
on     t2.event_no = t1.event_no
and    t1.bs_id <> t2.max_bs_id;

event_no | bs_id
-------: | ----:
 1692163 | 40672
 1692163 | 40672
 1723264 | 67460
 1723264 | 67499
 1723265 | 67383
 1723266 | 67735
 1723266 | 67423
 1723266 | 67969
 1723266 | 67501

dbfiddle here