Subscribe Bookmark RSS Feed

Creating a data subset based on row order

owiuser

Community Trekker

Joined:

Sep 23, 2011

I have a table of bird survey data collected at numerous stations. Most stations have had multiple surveys conducted on different dates. So my table has multiple rows for each station, each of these rows having information for the different surveys. I've sorted my table by station and then by survey date.  Now I'm trying to create a subset of this table in which there is one record (row) per station and when there are multiple records (rows) for a given station, I want to select the first row in the table.  I've been trying the subset and data filter features without any success.   Any ideas?

I've attached the table.   Station ID is Column ST_CN; survey date is Column MS_DA. 

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

You can use Summary (group by ST_CN) and then Update the Summary table with origiginal table (matching columns ST_CN). Update by matchng columns seems to use the first match (row) in case of multiple instances.

Can also be done with a script:

(Data Table( "sttn_survey_join2_unocc" ) << summary( group( :ST_CN ) )) <<

Update(

          With( Data Table( "sttn_survey_join2_unocc" ) ),

          Match Columns( :ST_CN = :ST_CN )

)

2 REPLIES
Solution

You can use Summary (group by ST_CN) and then Update the Summary table with origiginal table (matching columns ST_CN). Update by matchng columns seems to use the first match (row) in case of multiple instances.

Can also be done with a script:

(Data Table( "sttn_survey_join2_unocc" ) << summary( group( :ST_CN ) )) <<

Update(

          With( Data Table( "sttn_survey_join2_unocc" ) ),

          Match Columns( :ST_CN = :ST_CN )

)

owiuser

Community Trekker

Joined:

Sep 23, 2011

The script worked perfectly.  Thank you!