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.
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.
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;