Select only one first value for every id

[Anonymous]

Good day!

I have source table:

City	Value
City1	a
City1	b
City1	c
City2	by
City2	d
City3	zc
City3	s

I need to get result table, which will have only one firts value for every City:

City	Value
City1	a
City2	by
City3	zc

[tamilarasu · Accepted solution]

Hi,

Use FirstValue() syntax in your load script.

Temp:
LOAD * INLINE [
    City, Value
    City1, a
    City1, b
    City1, c
    City2, by
    City2, d
    City3, zc
    City3, s
];

NoConcatenate
Data:
Load City,
FirstValue(Value) as Value
Resident Temp Group by City;

DROP Table Temp;

[Mark_Little]

Hi,

Maybe approach in script?

Load

     City,

     Value

     IF(Rowno() = 1,

          1,

          IF(PEEK(City),-1 <> City,1,) as FirstValue

From '.....'

Group by City;

Then in the set just show where FirstValue = 1

Mark

[Anonymous]

Thanks, dear!

But if i have an example:

City	Value
City1	a
City1	b
City1	-
City2	by
City2	d
City3	zc
City3	s

your code returns for city 1 "-".

[tamilarasu]

Hi Andrey,

Try something like below

Temp:
LOAD * INLINE [
    City, Value
    City1, 
    City1, b
    City1, a
    City2, by
    City2, d
    City3, zc
    City3, s
];

NoConcatenate
Data:
Load City, 
FirstValue(Value) as Value 
Resident Temp where len(Trim(Value))>0 Group by City ;

DROP Table Temp;