StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>As far as I know Sybase also supports window functions. The best I can come up with is this: </p> <pre><code>select transaction_date, amount, currency, conversion_rate, conversion_date as effective_conversion_date, amount_usd from ( select t.transaction_date, t.amount, t.currency, cc.conversion_rate, cc.conversion_date, t.amount * cc.conversion_rate as amount_usd, dense_rank() over (partition by transaction_date, currency order by cc.conversion_date desc) as rn, max(cc.conversion_date) over (partition by currency) as max_conv_date from transactions t left join currency_conversion cc on t.currency = cc.from_currency and cc.to_currency = 'USD' ) t where (conversion_date = max_conv_date or rn = 1) order by transaction_date; </code></pre> <p>The trick is to leave out the transaction_date from the outer join and calculate the latest conversion_date on the fly using a window function. At the same time <code>dense_rank()</code> applies a value to the "latest" conversion date. That will return multiple rows for a single transaction if there are several currency_conversions for that currency.</p> <p>Those unwanted rows are then filtered out by only selecting those where either the transaction_date equals the latest conversion date or the one with the latest conversion date.</p> <p><strong>BUT</strong>: I don't think this is very efficient though. If your tables are really big (which is to be expected I guess) this might not perform very well. </p> <p>Here is a little SQL Fiddle example: <a href="http://sqlfiddle.com/#!12/63ef4/1" rel="nofollow">http://sqlfiddle.com/#!12/63ef4/1</a> <br/> (it is using Postgres and ANSI SQL but I think the query should run on Sybase as well)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload