Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to write a SELECT distinct with multiple ID columns?

    Hi,
    on DB2 10.1 on Linux I have a table with following data (simplified sample):
    Code:
    ID1         ID2         ID3         COLA       COLB       OTHER1     OTHER2
    ----------- ----------- ----------- ---------- ---------- ---------- -------
           2013         100           5 AAA        111        abc        def
           2014          98           7 BBB        222        ghi        jkl
           2014          99           2 BBB        222        mno        prs
           2014          99           5 BBB        222        tuv        zab
    I need to look into COLA and COLB and display there distinct values. Distinct values are (AAA, 111) and (BBB, 222). For ID1, ID2 and ID3 I need to get maximum ID1 and in the same row maximum ID2 and in the same row maximum ID3. Getting uniq value from ID1, ID2 and ID3 then display values for other columns like OTHER1 and OTHER2.

    The end result should be:
    Code:
    ID1         ID2         ID3         COLA       COLB       OTHER1     OTHER2
    ----------- ----------- ----------- ---------- ---------- ---------- -------
           2013         100           5 AAA        111        abc        def
           2014          99           5 BBB        222        tuv        zab
    Sample explanation:
    AAA, 111 combination is only in first row, so just display first row. But combination CCC, 222 are in last three rows. So first we need to look into ID1 to get maximum value (last three rows satisfy condition). Then in the same row we need to look for maximum value for ID2 this is 99 (last two rows satisfy condition)and in the same row we need to look into ID3 maximum value and this is 5 (only last row satisfy condition). So last row is the row I need to display for (CCC, 222) combination.

    How to write such an select statement to get this output?

    I tried something like:
    select * from admin.tab where (id1, id2, id3) in (select max(id1), max(id2), max(id3) from admin.tab group by cola, colb)
    but obviously this is wrong, because each of max functions search for max value in whole table, but I would like to restrict to one row.
    Thanks

    SQLs to recreate above data:
    create table admin.tab (id1 int not null, id2 int not null, id3 int not null, colA char(10), colB char(10), other1 char(10), other2 char(10), primary key (id1, id2, id3));
    insert into admin.tab values (2013, 100, 5, 'AAA', '111', 'abc', 'def');
    insert into admin.tab values (2014, 98, 7, 'BBB', '222', 'ghi', 'jkl');
    insert into admin.tab values (2014, 99, 2, 'BBB', '222', 'mno', 'prs');
    insert into admin.tab values (2014, 99, 5, 'BBB', '222', 'tuv', 'zab');
    select * from admin.tab;
    Last edited by grofaty; 06-17-15 at 09:56.

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    select id1, id2, id3, cola, colb, other1, other2
    from (
    select 
      id1, id2, id3, cola, colb, other1, other2
    , rownumber() over (partition by cola, colb order by id1 desc, id2 desc, id3 desc) rn_
    from admin.tab
    ) 
    where rn_=1
    Regards,
    Mark.

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    mark.b, I have added a lot of more records to cover all of the possibilities and your SQL is exactly! what I need.
    Thanks a million.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •