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!!

"jhking" <jhking_at_airmail.net> wrote in message news:bg6ncn$jb7_at_library1.airnews.net...
> howzabout
> SELECT thisyear , count( distinct currency )
> FROM input_table
> GROUP BY thisyear
> HAVING count( distinct currency ) = 1
> Fredrik Wahlgren wrote:
> > 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:37:27 CDT