CODEWITHSUNDEEP
sqloracle-databasemaxnetezza
dropx
dropx

Reputation: 25

How to display max value from the group of column. max value should be different from pattern

I have table A like this: 

ID | L1   |L2   | Date         
-- |----- |---- | ---------
1  |  A   | B   | 2003-01-01
---|------|-----|----------
2  |  A   | B   | 2004-05-01
---|------|-----|----------
3  |  B   | C   | 2003-01-01
---|------|-----|-----------
4  |  B   | C   | 9999-12-31
---|------|-----|-----------
5  |  C   | D   | 1998-02-03
---|------|-----|-----------
6  |  C   | D   | 2004-05-01

Normal situation is when for a pair of letters (AB, BC, CD) is one date = '9999-12-31'. So for two pairs of letters (AB, CD) I miss the date '9999-12-31' and I need to write a query to see those letters and IDs.

I wrote a query like (to see the max date of pair of letters)

select distinct L1, L2, max(date) from A GROUP BY 
L1, L2
having max(date)<>'9999-12-31'

This query shows correct rows but I need to also see IDs and this is something I couldn't do. Does anyone knows how to write the query to see IDs also? ID is always unique

Thanks!!

Upvotes: 0

Views: 1136

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Try not exists:

select a.*
from a a
where not exists (select 1
                  from a a2
                  where a2.l1 = a.l1 and a2.l2 = a.l2 and
                        a2.date = '9999-12-31'
                 );

This assumes that the "date" column is stored as a string, or that you have date format settings so '9999-12-31' is interpreted correctly (as is suggested by your sample query). Otherwise, you can use:

a2.date = date '9999-12-31'

EDIT:

If you want the highest date for l1/l2` combos that don't have the maximum, that is easy:

select l1, l2, max(date)
from a
group by l1, l2
having max(date) < date '9999-12-31';

Upvotes: 0

user5683823
user5683823

Reputation:

Gordon offered a solution if you want all the rows for the pairs L1, L2 that don't have any date equal to 9999-12-31.

Here is a solution in case you were looking only for the pairs L1, L2 that don't have any date equal to 9999-12-31, and for those pair, you only wanted the row with the latest date.

with
     table_A ( id, l1, l2, dt ) as (
       select 1, 'A', 'B', date '2003-01-01' from dual union all
       select 2, 'A', 'B', date '2004-05-01' from dual union all
       select 3, 'B', 'C', date '2003-01-01' from dual union all
       select 4, 'B', 'C', date '9999-12-31' from dual union all
       select 5, 'C', 'D', date '1999-02-03' from dual union all
       select 6, 'C', 'D', date '2004-05-01' from dual
     )
select id, l1, l2, dt
from (
       select id, l1, l2, dt,
              row_number () over (partition by l1, l2 order by dt desc) rn
       from   table_A
     )
where rn = 1
and   dt != date '9999-12-31'
;

    ID L1 L2 DT                
------ -- -- -------------------
     2  A  B 2004-05-01 00:00:00
     6  C  D 2004-05-01 00:00:00

Upvotes: 1

Related Questions