Re: Tricky query

Hi

It ceratinly works.I didn't realize that MIN() could be used this way. I 've just tested it on SQL Server. I don't know yet which query I'll use but I will paste your reply into the code.

Thanks again,
Fredrik

"jhking" <jhking_at_airmail.net> wrote in message news:bg6qjb$c5c_at_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 Mon Jul 28 2003 - 05:02:33 CDT