Skip navigation links

MySQL Forums :: Data Warehouse :: Mysql supports 'partition by' analytical function?


Advanced Search

Re: Mysql supports 'partition by' analytical function?
Posted by: Bhanu Nadendla ()
Date: September 08, 2008 09:09AM

Hi Roland,

Base table Data wiht two columns (currency_code | currency_date).

Currency_code| Currency_date
USD | 10/12/2008
USD | 10/13/2008
USD | 10/14/2008
USD | 10/15/2008
AUD | 10/11/2008
AUD | 10/12/2008
AUD | 10/18/2008

My code:
SELECT o.currency_code, o.currency_date,
FIND_IN_SET(
o.currency_date
, (
SELECT GROUP_CONCAT(currency_date ORDER BY currency_date)
FROM Table1 i
WHERE i.currency_code = o.currency_code
)
) AS rate_index
FROM Table1 o

Output:
Currency_code| Currency_date | Rate_index
USD | 10/12/2008 | 1
USD | 10/13/2008 | 2
USD | 10/14/2008 | 3
USD | 10/15/2008 | 4
AUD | 10/11/2008 | 1
AUD | 10/12/2008 | 2
AUD | 10/18/2008 | 3

Expected outout:
Currency_code | Currency_date | Rate_index
USD | 10/12/2008 | 4
USD | 10/13/2008 | 3
USD | 10/14/2008 | 2
USD | 10/15/2008 | 1
AUD | 10/11/2008 | 3
AUD | 10/12/2008 | 2
AUD | 10/18/2008 | 1

The second workaround which you ahve provided works fine.

The only issue is, both these wrokarounds are taking longer time to get the results.


Thanks
Bhanu

Options: ReplyQuote


Subject Views Written By Posted
Mysql supports 'partition by' analytical function? 40682 Bhanu Nadendla 09/05/2008 03:18AM
Re: Mysql supports 'partition by' analytical function? 25353 Roland Bouman 09/05/2008 04:46AM
Re: Mysql supports 'partition by' analytical function? 10068 Bhanu Nadendla 09/08/2008 08:40AM
Re: Mysql supports 'partition by' analytical function? 8110 Roland Bouman 09/08/2008 08:52AM
Re: Mysql supports 'partition by' analytical function? 8466 Bhanu Nadendla 09/08/2008 09:09AM
Re: Mysql supports 'partition by' analytical function? 7230 Roland Bouman 09/08/2008 09:19AM
Re: Mysql supports 'partition by' analytical function? 6417 Bhanu Nadendla 09/08/2008 09:23AM
Re: Mysql supports 'partition by' analytical function? 6612 Roland Bouman 09/08/2008 10:22AM
Re: Mysql supports 'partition by' analytical function? 8233 Bhanu Nadendla 09/09/2008 05:26AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.