topic Re: Selecting only rows with max value? in Discussions

Selecting only rows with max value?

ChrisNally — Tue, 11 Jul 2017 13:22:56 GMT

Rookie to scripting and looking for some scripting help:

I have a table of final specifications with all incarnations listed from version 1 to version 23 (in a column headed 'VERSION'. Table is nearly 1000 rows and will be updated as spec changes.

I am attempting to select only the rows with the latest version ( at the moment version 23 but will change over time) and delete all other rows from the table. I would therefore be looking to select the max value for this column rather than a specific number to make it futureproof. I have spent a few hours today trying to work it out but no success. Any help would be much appreciated.

Chris

Re: Selecting only rows with max value?

pmroz — Tue, 11 Jul 2017 13:39:53 GMT

This will do it.

dt = New Table( "Untitled 2",
	Add Rows( 6 ),
	New Column( "VERSION", Numeric, "Continuous",
		Format( "Best", 12 ), Set Values( [1, 2, 3, 1, 2, 3] )
	),
	New Column( "Data", Character, "Nominal",
		Set Values( {"a", "b", "c", "d", "e", "f"} )
	)
);

max_version = colmax(column(dt, "VERSION"));

dt << select where(as column(dt, "VERSION") == max_version) << invert row selection << delete rows;

Re: Selecting only rows with max value?

ChrisNally — Tue, 11 Jul 2017 14:20:31 GMT

Thank you, this worked perfectly, very much appreciated.

Chris