Select first value only

[#1]

In my script I have defined a table that loads in fields called account_nbr and production_date. I am limiting these field values that are loaded in by only loading in those where another field equals a certain value. This is what it looks like:

Table2:

LOAD

account_nbr

production_date

Resident Table1

where fieldName = 1;

My goal is to only load the first row for each account_nbr where fieldName = 1. Is this possible? Right now I am getting multiple rows loaded for each account_nbr since fieldName = 1 for more than one production_date. To put it more simply, I am only interested in loading the account_nbr and production_date values where the fieldName = 1 for the first time, and nothing after that. Thanks for the help.

[Greg_Williams]

Have you tried where exists()?

[m_woolf]

Try putting First 1 above the load.

[#4]

How would this only select the top row for each account_nbr?

[#5]

This only loads one row. I'm interested in loading the first row for each of several account numbers.

[maxgro]

try with (in bold the most important piece of code)

table:

load rowno() as id, * inline [

account_nbr, production_date, fieldName

1,11,0

1,2,0

1,31,1

1,4,1

1,5,0

2,61,0

2,7,1

2,8,1

];

final:

NoConcatenate

load *

Resident table

where fieldName = 1

and peek(account_nbr) <> account_nbr

order by account_nbr, id;

DROP Table table;

EDIT: if you want the first by date, order by date instead of id

[eduardo_sommer · Accepted solution]

Use this:

Table2:

LOAD

account_nbr

first(production_date) as production_date

Resident Table1

where fieldName = 1

Group by account_nbr;

Eduardo