Re: Tricky query

Hi

This was the input I needed. It didn't quite work but I now know how to do this. The problem was that your suggested query only returned the years for which there was only one currency, I want the currency as well. Anyway, using your reply as a starter, I eneded up with this:

SELECT DISTINCT thisyear, currency FROM input_table WHERE thisyear IN

(SELECT thisyear

FROM input_table

GROUP BY thisyear

HAVING count( distinct currency ) = 1)

And this works beautifully :-)

Thanks!!

"Fredrik Wahlgren" <fredrik.p.wahlgren_at_mailbox.swipnet.se> wrote in message news:QTAVa.40436$zL.221_at_news1.bredband.com...
> Hi
>
> I have a table with a few columns like year, period, account,company,
> currency and so on. This table will be joined with one or several data
> tables, data00, data01
> In order to make a better main query, I want to select the years and the
> currency for which there is only one currency. Now, If i make a query
like
> this
>
> SELECT DISTINCT thisyear, currency FROM input_table
>
> This is what I get
>
> thisyear currency
> 94 USD // Don't want this row, two currencies used for '94
> 94 GBP // Don't want this row, two currencies used for '94
> 95 EUR
> 96 EUR
> 97 EUR
> 98 EUR // Don't want this row, three currencies used for '98
> 98 SEK // Don't want this row, three currencies used for '98
> 98 USD // Don't want this row, three currencies used for '98
> 99 EUR
>
>
> I need both the year and the currency since I will save these in an array,
> The idea is to make a main query that looks like this. And , yes, this
makes
> the query
> significantly faster
>
> SELECT this, that from data94 a, input_table b
> WHERE a.currency = b.currency AND ... //
> Non optimised condition
> UNION ALL SELECT this, that from data95 a, input_table b
> WHERE a.currency = 'EUR' AND ... //
> Optimised condition
> UNION ALL SELECT this, that from data96 a, input_table b
> WHERE a.currency = 'EUR' AND ...
>
> Thanks in advance,
> Fredrik
>
>
Received on Mon Jul 28 2003 - 04:53:00 CDT