Re: Tricky query

From: jhking <jhking_at_airmail.net>
Date: Tue, 29 Jul 2003 16:57:48 -0500
Message-ID: <bg6qjb$c5c@library1.airnews.net>

Glad I could help.
FYI:
SELECT thisyear , min(currency) currency , count( distinct currency ) FROM input_table
GROUP BY thisyear
HAVING count( distinct currency ) = 1)

Will get you where you want to go without the IN clause. If count(distinct currency ) = 1
then min( currency ) is the currency. Actually, so is max(), I usually use min() for such things.

Fredrik Wahlgren wrote:

> 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 Tue Jul 29 2003 - 16:57:48 CDT