Analytical ordered function to get the first row in window

Database
Enthusiast

Analytical ordered function to get the first row in window

Hi,

I have this table.







Column1 Column2 Column3 Column5
Key0 3/8/2011 13:00 Entry New KJLUONMP
Key0 3/8/2011 14:00 Change New KJLUONMP
Key1 3/8/2011 16:00 Transfer KJLUONMP 
Key1 3/8/2011 19:00 Change out KJLUONMP 
Key1 3/24/2011 7:00 Change In KJLUONMP 
Key1 3/24/2011 9:00 Return XCDVGHNJ
Key1 4/10/2011 3:43 Load XCDVGHNJ
Key1 4/10/2011 4:43 Return XCDVGHNJ

This is my requirement

If Column3 = "Load"

  1.  Find the row with Column3 = "Transfer" in that window with Column1 as PARTITION BY and Column2 as ORDER BY.

  2. From this row get the value of Column5

So the the result I expect is this.







Column1 Column2 Column3 Column5 Result
Key0 3/8/2011 13:00 Entry New KJLUONMP  
Key0 3/8/2011 14:00 Change New KJLUONMP  
Key1 3/8/2011 16:00 Transfer KJLUONMP   
Key1 3/8/2011 19:00 Change out KJLUONMP   
Key1 3/24/2011 7:00 Change In KJLUONMP   
Key1 3/24/2011 9:00 Return XCDVGHNJ  
Key1 4/10/2011 3:43 Load XCDVGHNJ KJLUONMP 
Key1 4/10/2011 4:43 Return XCDVGHNJ  

I tried using this query

sel

CASE Column5

WHEN 'Load' then

min(Column5) OVER (PARTITION BY Column1

ORDER BY Column2 RESET WHEN Column5 = 'Transfer' ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

END AS Result

Problem I am facing is that, I cant use MIN or MAX function, I have to get the value of Column5 exactly where "Transfer" is encountered first time.

Any help is appreciated.

Regards,

Abin

2 REPLIES
Enthusiast

Re: Analytical ordered function to get the first row in window

I think the table data in my previous post didnt come as expected, here is the table, result column is what I expect as output.













Column1

Column2

Column3

Column5

Result

Key0

3/8/2011 13:00

Entry New

KJLUONMP

Key0

3/8/2011 14:00

Change New

KJLUONMP

Key1

3/8/2011 16:00

Transfer

KJLUONMP 

Key1

3/8/2011 19:00

Change out

KJLUONMP 

Key1

3/24/2011 7:00

Change In

KJLUONMP 

Key1

3/24/2011 9:00

Return

XCDVGHNJ

Key1

4/10/2011 3:43

Load

XCDVGHNJ

KJLUONMP 

Key1

4/10/2011 4:43

Return

XCDVGHNJ

 

Abin

Junior Contributor

Re: Analytical ordered function to get the first row in window

Hi Abin,

your data/expected result is not readable, better provide DDL + INSERTs.

Might there be multiple rows with 'transfer' before the 'load'?

If yes, which one to choose, the oldest or the newest one?

Dieter