Tricky query for me, should be simple for you 
Author Message
 Tricky query for me, should be simple for you

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



Fri, 13 Jan 2006 09:24:17 GMT
 Tricky query for me, should be simple for you

Try:

SELECT thisyear, currency
FROM input_table
GROUP BY thisyear, currency
HAVING COUNT (*) = 1

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql

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



Sat, 14 Jan 2006 21:38:51 GMT
 Tricky query for me, should be simple for you
untested...

select *
from tb t1
where t1.year in (select year
from tb t2
group by t2.year
having count(*)=1)

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net


Quote:
> 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



Sat, 14 Jan 2006 21:42:10 GMT
 Tricky query for me, should be simple for you

:-)

select yr,cur
from(
select yr=91,cur='abc'
union all select 91,'abc'
union all select 91,'def'
union all select 92,'abc'
union all select 92,'def'
union all select 93,'abc'
)x
group by yr,cur
having count(*)=1

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net


  Try:

  SELECT thisyear, currency
  FROM input_table
  GROUP BY thisyear, currency
  HAVING COUNT (*) = 1

  --
  Tom

  ---------------------------------------------------------------
  Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
  SQL Server MVP
  Columnist, SQL Server Professional
  Toronto, ON Canada
  www.pinnaclepublishing.com/sql


  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



Sat, 14 Jan 2006 21:47:21 GMT
 Tricky query for me, should be simple for you
Hi

Problem solved. This problem has frustrated me for some time now, last night
I went to bed at 3 am. Acually i crossposted this problem to an Oracle
newsgroup.

Anyway, someone suggested this query

SELECT thisyear, count( distinct currency )
FROM input_table
GROUP BY thisyear
HAVING count( distinct currency ) = 1

It returns the years allright but not the currency. So, I came up with this
query

SELECT DISTINCT thisyear, currency FROM input_table WHERE thisyear IN
(SELECT thisyear
FROM input_table
GROUP BY thisyear
HAVING count( distinct currency ) = 1)

This query works beautifully...

I want to thank those of you who took time to make suggestions

Thanks,
Fredrik


Quote:
> 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



Fri, 13 Jan 2006 10:50:04 GMT
 Tricky query for me, should be simple for you
Fredrik,

  You could also do this:

select thisyear, min(currency) as currency
from input_table
group by thisyear
having min(currency) = max(currency)

-- Steve Kass
-- Drew University
-- Ref: 3BBCDF6E-266D-4EC0-86D9-1C6233A6DF7C

Quote:

>Hi

>Problem solved. This problem has frustrated me for some time now, last night
>I went to bed at 3 am. Acually i crossposted this problem to an Oracle
>newsgroup.

>Anyway, someone suggested this query

>SELECT thisyear, count( distinct currency )
>FROM input_table
>GROUP BY thisyear
>HAVING count( distinct currency ) = 1

>It returns the years allright but not the currency. So, I came up with this
>query

>SELECT DISTINCT thisyear, currency FROM input_table WHERE thisyear IN
>(SELECT thisyear
>FROM input_table
>GROUP BY thisyear
>HAVING count( distinct currency ) = 1)

>This query works beautifully...

>I want to thank those of you who took time to make suggestions

>Thanks,
>Fredrik



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



Sun, 15 Jan 2006 01:45:39 GMT
 Tricky query for me, should be simple for you

Steve,

Assuming that it is possible to have > 1 row for a given combination of (thisyear, currency), would that query not give you a hit when you had > 1 hit?  Here's what I mean:


(
  thisyear int  not null
, currency char (3) not null
)






-- solution 1
select thisyear, min(currency) as currency

group by thisyear
having min(currency) = max(currency)

-- solution 2
select thisyear, min(currency) as currency

group by thisyear
having count (*) = 1

-- solution 3
select thisyear, currency

group by thisyear, currency
having count (*) = 1

The min = max returns a row for (2000, 'EUR') but - if I interpreted the problem correctly - he would not want that row.  thus, I believe that solutions 2 and 3 may give the desired result.

--
   Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinnaclepublishing.com/sql
.
Fredrik,

  You could also do this:

select thisyear, min(currency) as currency
from input_table
group by thisyear
having min(currency) = max(currency)

-- Steve Kass
-- Drew University
-- Ref: 3BBCDF6E-266D-4EC0-86D9-1C6233A6DF7C

Quote:

>Hi

>Problem solved. This problem has frustrated me for some time now, last night
>I went to bed at 3 am. Acually i crossposted this problem to an Oracle
>newsgroup.

>Anyway, someone suggested this query

>SELECT thisyear, count( distinct currency )
>FROM input_table
>GROUP BY thisyear
>HAVING count( distinct currency ) = 1

>It returns the years allright but not the currency. So, I came up with this
>query

>SELECT DISTINCT thisyear, currency FROM input_table WHERE thisyear IN
>(SELECT thisyear
>FROM input_table
>GROUP BY thisyear
>HAVING count( distinct currency ) = 1)

>This query works beautifully...

>I want to thank those of you who took time to make suggestions

>Thanks,
>Fredrik



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



Sun, 15 Jan 2006 02:30:38 GMT
 Tricky query for me, should be simple for you
Tom,

  Frederik's second post in this thread says the following query works:

SELECT DISTINCT thisyear, currency FROM input_table WHERE thisyear IN
(SELECT thisyear
FROM input_table
GROUP BY thisyear
HAVING count( distinct currency ) = 1)

So I assumed he wanted the (year, currency) pairs where there was
only one distinct currency, regardless of how many times that one
currency appeared in the table that year.

The min() = max() takes care of that, but count(*) doesn't.

Steve

Quote:

>Steve,

>Assuming that it is possible to have > 1 row for a given combination of (thisyear, currency), would that query not give you a hit when you had > 1 hit?  Here's what I mean:


>(
>  thisyear int  not null
>, currency char (3) not null
>)






>-- solution 1
>select thisyear, min(currency) as currency

>group by thisyear
>having min(currency) = max(currency)

>-- solution 2
>select thisyear, min(currency) as currency

>group by thisyear
>having count (*) = 1

>-- solution 3
>select thisyear, currency

>group by thisyear, currency
>having count (*) = 1

>The min = max returns a row for (2000, 'EUR') but - if I interpreted the problem correctly - he would not want that row.  thus, I believe that solutions 2 and 3 may give the desired result.



Sun, 15 Jan 2006 06:10:20 GMT
 Tricky query for me, should be simple for you

Steve,

I'm just wondering if it is a coincidence.  Here's what he said in the OP:

"In order to make a better main query, I want to select the years and the currency for which there is only one currency. "

Note that he didn't say "... one distinct currency".  That's why I'm wondering about the possibility of duplicates.  If the table had a unique constraint on (thisyear, currency), then it would not be an issue.  I guess if we had the full DDL for the table, we'd be able to figure it out.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql

Tom,

  Frederik's second post in this thread says the following query works:

SELECT DISTINCT thisyear, currency FROM input_table WHERE thisyear IN
(SELECT thisyear
FROM input_table
GROUP BY thisyear
HAVING count( distinct currency ) = 1)

So I assumed he wanted the (year, currency) pairs where there was
only one distinct currency, regardless of how many times that one
currency appeared in the table that year.

The min() = max() takes care of that, but count(*) doesn't.

Steve

Quote:

>Steve,

>Assuming that it is possible to have > 1 row for a given combination of (thisyear, currency), would that query not give you a hit when you had > 1 hit?  Here's what I mean:


>(
>  thisyear int  not null
>, currency char (3) not null
>)






>-- solution 1
>select thisyear, min(currency) as currency

>group by thisyear
>having min(currency) = max(currency)

>-- solution 2
>select thisyear, min(currency) as currency

>group by thisyear
>having count (*) = 1

>-- solution 3
>select thisyear, currency

>group by thisyear, currency
>having count (*) = 1

>The min = max returns a row for (2000, 'EUR') but - if I interpreted the problem correctly - he would not want that row.  thus, I believe that solutions 2 and 3 may give the desired result.



Sun, 15 Jan 2006 13:23:50 GMT
 Tricky query for me, should be simple for you
Tom,

  I guess it could be either.  Just because he said
select (distinct count) worked beautifully doesn't explain
what he wants, since all these will work the same way on
data without duplicate year,currency pairs.

  He's got a choice and some discussion to explain the
difference - what more could he need!

Steve

Quote:

>Steve,

>I'm just wondering if it is a coincidence.  Here's what he said in the OP:

>"In order to make a better main query, I want to select the years and the currency for which there is only one currency. "

>Note that he didn't say "... one distinct currency".  That's why I'm wondering about the possibility of duplicates.  If the table had a unique constraint on (thisyear, currency), then it would not be an issue.  I guess if we had the full DDL for the table, we'd be able to figure it out.



Sun, 15 Jan 2006 14:20:51 GMT
 Tricky query for me, should be simple for you

Steve,

Yep.  Good specs = good code.  ;-)

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql

Tom,

  I guess it could be either.  Just because he said
select (distinct count) worked beautifully doesn't explain
what he wants, since all these will work the same way on
data without duplicate year,currency pairs.

  He's got a choice and some discussion to explain the
difference - what more could he need!

Steve

Quote:

>Steve,

>I'm just wondering if it is a coincidence.  Here's what he said in the OP:

>"In order to make a better main query, I want to select the years and the currency for which there is only one currency. "

>Note that he didn't say "... one distinct currency".  That's why I'm wondering about the possibility of duplicates.  If the table had a unique constraint on (thisyear, currency), then it would not be an issue.  I guess if we had the full DDL for the table, we'd be able to figure it out.



Sun, 15 Jan 2006 14:31:28 GMT
 Tricky query for me, should be simple for you

Hi

It certainly is possible to have > 1 row for a given combination of (thisyear, currency).In the example you give me, there is only one currency used for the year 2000. I certainly want that row. In the example below, my query would look something like this

SELECT this that from Data00 a, table b
WHERE a.currency = 'EUR' AND ...
UNION ALL
SELECT this that from Data01a, table b
WHERE a.currency = 'EUR' AND ...
UNION ALL
SELECT this that from Data02, table b
WHERE a.currency = 'USD' AND ...
UNION ALL
SELECT this that from Data03 table b
WHERE a.currency = 'CAD' AND...

If there woild be an additional entry in 'table' like this:

I wouldn't be able to optimize as much. The query would have to be:

SELECT this that from Data00 a, table b
WHERE a.currency = b.currency
UNION ALL
SELECT this that from Data01a, table b
WHERE a.currency = 'EUR' AND ...
UNION ALL
SELECT this that from Data02, table b
WHERE a.currency = 'USD' AND ...
UNION ALL
SELECT this that from Data03 table b
WHERE a.currency = 'CAD' AND...

  Steve,

  Assuming that it is possible to have > 1 row for a given combination of (thisyear, currency), would that query not give you a hit when you had > 1 hit?  Here's what I mean:


  (
    thisyear int  not null
  , currency char (3) not null
  )






  -- solution 1
  select thisyear, min(currency) as currency

  group by thisyear
  having min(currency) = max(currency)

  -- solution 2
  select thisyear, min(currency) as currency

  group by thisyear
  having count (*) = 1

  -- solution 3
  select thisyear, currency

  group by thisyear, currency
  having count (*) = 1

  The min = max returns a row for (2000, 'EUR') but - if I interpreted the problem correctly - he would not want that row.  thus, I believe that solutions 2 and 3 may give the desired result.

  --
     Tom

  ----------------------------------------------------
  Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
  SQL Server MVP
  Columnist, SQL Server Professional
  Toronto, ON   Canada
  www.pinnaclepublishing.com/sql
  .

  Fredrik,

    You could also do this:

  select thisyear, min(currency) as currency
  from input_table
  group by thisyear
  having min(currency) = max(currency)

  -- Steve Kass
  -- Drew University
  -- Ref: 3BBCDF6E-266D-4EC0-86D9-1C6233A6DF7C


  >Hi
  >
  >Problem solved. This problem has frustrated me for some time now, last night
  >I went to bed at 3 am. Acually i crossposted this problem to an Oracle
  >newsgroup.
  >
  >Anyway, someone suggested this query
  >
  >SELECT thisyear, count( distinct currency )
  >FROM input_table
  >GROUP BY thisyear
  >HAVING count( distinct currency ) = 1
  >
  >It returns the years allright but not the currency. So, I came up with this
  >query
  >
  >SELECT DISTINCT thisyear, currency FROM input_table WHERE thisyear IN
  >(SELECT thisyear
  >FROM input_table
  >GROUP BY thisyear
  >HAVING count( distinct currency ) = 1)
  >
  >This query works beautifully...
  >
  >I want to thank those of you who took time to make suggestions
  >
  >Thanks,
  >Fredrik
  >
  >


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



Sat, 14 Jan 2006 23:34:04 GMT
 Tricky query for me, should be simple for you

OK, so you want the years for which all currencies are the same, regardless of the number of currencies.  Then either of the following two solutions - using my original build script - will do it:

select thisyear, min (currency)

group by thisyear, currency
having count (distinct currency) = 1

select thisyear, min(currency) as currency

group by thisyear
having min(currency) = max(currency)

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql

Hi

It certainly is possible to have > 1 row for a given combination of (thisyear, currency).In the example you give me, there is only one currency used for the year 2000. I certainly want that row. In the example below, my query would look something like this

SELECT this that from Data00 a, table b
WHERE a.currency = 'EUR' AND ...
UNION ALL
SELECT this that from Data01a, table b
WHERE a.currency = 'EUR' AND ...
UNION ALL
SELECT this that from Data02, table b
WHERE a.currency = 'USD' AND ...
UNION ALL
SELECT this that from Data03 table b
WHERE a.currency = 'CAD' AND...

If there woild be an additional entry in 'table' like this:

I wouldn't be able to optimize as much. The query would have to be:

SELECT this that from Data00 a, table b
WHERE a.currency = b.currency
UNION ALL
SELECT this that from Data01a, table b
WHERE a.currency = 'EUR' AND ...
UNION ALL
SELECT this that from Data02, table b
WHERE a.currency = 'USD' AND ...
UNION ALL
SELECT this that from Data03 table b
WHERE a.currency = 'CAD' AND...

  Steve,

  Assuming that it is possible to have > 1 row for a given combination of (thisyear, currency), would that query not give you a hit when you had > 1 hit?  Here's what I mean:


  (
    thisyear int  not null
  , currency char (3) not null
  )






  -- solution 1
  select thisyear, min(currency) as currency

  group by thisyear
  having min(currency) = max(currency)

  -- solution 2
  select thisyear, min(currency) as currency

  group by thisyear
  having count (*) = 1

  -- solution 3
  select thisyear, currency

  group by thisyear, currency
  having count (*) = 1

  The min = max returns a row for (2000, 'EUR') but - if I interpreted the problem correctly - he would not want that row.  thus, I believe that solutions 2 and 3 may give the desired result.

  --
     Tom

  ----------------------------------------------------
  Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
  SQL Server MVP
  Columnist, SQL Server Professional
  Toronto, ON   Canada
  www.pinnaclepublishing.com/sql
  .

  Fredrik,

    You could also do this:

  select thisyear, min(currency) as currency
  from input_table
  group by thisyear
  having min(currency) = max(currency)

  -- Steve Kass
  -- Drew University
  -- Ref: 3BBCDF6E-266D-4EC0-86D9-1C6233A6DF7C


  >Hi
  >
  >Problem solved. This problem has frustrated me for some time now, last night
  >I went to bed at 3 am. Acually i crossposted this problem to an Oracle
  >newsgroup.
  >
  >Anyway, someone suggested this query
  >
  >SELECT thisyear, count( distinct currency )
  >FROM input_table
  >GROUP BY thisyear
  >HAVING count( distinct currency ) = 1
  >
  >It returns the years allright but not the currency. So, I came up with this
  >query
  >
  >SELECT DISTINCT thisyear, currency FROM input_table WHERE thisyear IN
  >(SELECT thisyear
  >FROM input_table
  >GROUP BY thisyear
  >HAVING count( distinct currency ) = 1)
  >
  >This query works beautifully...
  >
  >I want to thank those of you who took time to make suggestions
  >
  >Thanks,
  >Fredrik
  >
  >


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



Mon, 16 Jan 2006 13:39:19 GMT
 
 [ 13 post ] 

 Relevant Pages 

1. Simple query fails - what am I missing?

2. Tricky SQL that _should_ be simple...

3. Simple Question - I am sure

4. Simple one what am i doing wrong

5. Simple set variable, what am I missing

6. Embarassingly simple SQL question - what am I missing?!

7. Simple question..I am running a stored procedure from SQL PLUS

8. Simple question..I am running a stored procedure from SQL PLUS

9. Tricky Query Problem

10. tricky CASE WHEN query...

11. A Tuff Tricky One - Please Help with Queries

12. tricky query (for me atleast)


 
Powered by phpBB® Forum Software