Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-08-2016
08:37 PM

12-08-2016
08:37 PM

I have following 3 columns (Order, Date, Price) in my table - Sequence I need to generate.

Based on Order_Date sorting (Ascending), if my Price drops, then the Sequence should change else remains unchanged.

Can we be able to write this in a single/multiple SELECT sql statement (with or without Recursive)?

Order | Order_Date | Price | Sequence | |

1 | 1/1/2010 | 100 | 1 | |

2 | 1/2/2010 | 200 | 1 | |

3 | 1/3/2010 | 300 | 1 | |

4 | 1/4/2010 | 100 | 2 | Price gets dropped from 300 to 100 - so sequence increased to 2 |

5 | 1/5/2010 | 500 | 2 | Price gets increased from 100 to 500 - so sequence number remain unchanged |

6 | 1/6/2010 | 200 | 3 | Price gets dropped from 500 to 200 - so sequence increased to 3 |

7 | 1/7/2010 | 100 | 4 | Price gets dropped from 200 to 100 - so sequence increased to 4 |

8 | 1/8/2010 | 600 | 4 | Price gets increased from 100 to 600 - so sequence number remain unchanged |

9 | 1/9/2010 | 500 | 5 | Price gets dropped from 600 to 500 - so sequence increased to 5 |

10 | 1/10/2010 | 700 | 5 | Price gets increased from 500 to 700 - so sequence number remain unchanged |

11 | 1/11/2010 | 700 | 5 | Price remain unchanged in 700 - so sequence number remain unchanged |

12 | 1/12/2010 | 300 | 6 | Price gets dropped from 700 to 300 - so sequence increased to 5 |

Solved! Go to Solution.

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-08-2016
11:42 PM

12-08-2016
11:42 PM

You need nested OLAP-functions, first add a 0/1 flag based on your logic and then sum it:

SELECT ... Sum(flag) Over (ORDER BY Order_col ROWS Unbounded Preceding) FROM ( SELECT ... CASE WHEN Min(Price) Over (ORDER BY Order_col ROWS BETWEEN 1 Preceding AND 1 Preceding) <= Price THEN 0 ELSE 1 END AS flag FROM tab ) AS dt

1 ACCEPTED SOLUTION

1 REPLY

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-08-2016
11:42 PM

12-08-2016
11:42 PM

You need nested OLAP-functions, first add a 0/1 flag based on your logic and then sum it:

SELECT ... Sum(flag) Over (ORDER BY Order_col ROWS Unbounded Preceding) FROM ( SELECT ... CASE WHEN Min(Price) Over (ORDER BY Order_col ROWS BETWEEN 1 Preceding AND 1 Preceding) <= Price THEN 0 ELSE 1 END AS flag FROM tab ) AS dt