Load distinct by a given column

[#1]

I have a table like this:

ColumnA - ColumnB - ColumnC
-------------------------------------------------
001 - ZZZ - 1
001-AAA-2
001-ZZZ-3
002-ZZZ-1
003-ZZZ-1
004-HHH-1
004-ZZZ-2
004-HHH-3

Those columns are in a resident table.

I want a second resident table that gets only the distinct rows by ColumnA and ColumnB bringing the lowest ColumnC. So, for that example above, it will return:

ColumnA - ColumnB - ColumnC
-------------------------------------------------
001 - ZZZ - 1
001-AAA-2
002-ZZZ-1
003-ZZZ-1
004-HHH-1
004-ZZZ-2

Any idea about how to do this?

[#2 · Accepted solution]

Hi,

try something like this:

tab1:
load * inline [
colA,colB,colC
001 , ZZZ , 1
001,AAA,2
001,ZZZ,3
002,ZZZ,1
003,ZZZ,1
004,HHH,1
004,ZZZ,2
004,HHH,3];

tab2:
load colA,colB,min(colC) as min
resident tab1
group by colA,colB;

Alex