Extract Max or MIn value from a set of examples

Question

Hi,

I am quite new to RapidMiner and I want to use it as an ETL-tool.

How do I extract the record (or example), in a group of records (examples) sharing the same identifer, for which a certain attribute have the maximal or minimal value?

For example, consider the following set of data rows, all having the same ID:

ID          Attribute1          Attribute2
xx         25                     Feb 19 2009
xx         16                     Jul 26 2007
xx         34                     Apr 14 2008

What operator should I use to get THE record which contains the max value of Attribute1. What operator should I use to retrieve the record containing the most recent date in Attribute2?

Thanks in advance!

ybritt · Answer

Thanks a lot for your help! Now I got it to work the way I wanted to

haddock · Answer

Hi,

I use MS SQL 2005, if I do this...use Tradestation
select O,H,L,MAX(C) as T From Updates

I get this..Column 'Updates.O' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

whereas this works fine.use Tradestation
select O,H,L,C From Updates

So my answer is that your SQL was not as perfect as it might be.

ybritt · Answer

Thanks!

However, the Build SQL Query dialog doesn't give me the option to use the Max() function. If I type in the expression below, the operator won't return any data

SELECT "MATERIAL", "PODATE", Max("PRICE") AS T
FROM "tbl_POHistory"

But, the strange thing is, THIS works:

SELECT MAX("PRICE")AS T
FROM "tbl_POHistory"

How shoud I interpret that?

(MATERIAL, PODATE and PRICE are all columns in the table tbl_POHistory from which I am retrieving the data)