Extract Max or MIn value from a set of examples

ybritt

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!

haddock

Hi,

You should read the help section for  "Read Database" operator, and the resources listed under "help".

ybritt

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)

haddock

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

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