Professional Web Applications Themes

Tricky query for me, should be simple for you - Microsoft SQL / MS SQL Server

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 "Fredrik Wahlgren" <swipnet.se> wrote in message news:dRAVa.40432$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 ...

Sponsored Links
  1. #1

    Default Re: 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


    "Fredrik Wahlgren" <swipnet.se> wrote in message news:dRAVa.40432$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



    Sponsored Links
    Tom Guest

  2. #2

    Default Re: 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


    "Fredrik Wahlgren" <swipnet.se> wrote in message
    news:dRAVa.40432$bredband.com... 
    like 
    makes 


    oj Guest

  3. #3

    Default Re: 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


    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    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


    "Fredrik Wahlgren" <swipnet.se> wrote in message news:dRAVa.40432$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



    oj Guest

  4. #4

    Default Re: 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

    Fredrik Wahlgren wrote:
     
    >like
    >

    >makes
    >

    >
    >
    >
    >[/ref]

    Steve Guest

  5. #5

    Default Re: 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:

    declare t table
    (
    thisyear int not null
    , currency char (3) not null
    )

    insert t values (2000, 'EUR')
    insert t values (2000, 'EUR')
    insert t values (2001, 'EUR')
    insert t values (2002, 'USD')
    insert t values (2003, 'CAD')

    -- solution 1
    select thisyear, min(currency) as currency
    from t
    group by thisyear
    having min(currency) = max(currency)

    -- solution 2
    select thisyear, min(currency) as currency
    from t
    group by thisyear
    having count (*) = 1

    -- solution 3
    select thisyear, currency
    from t
    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
    ..
    "Steve Kass" <edu> wrote in message news:OZk$phx.gbl...
    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

    Fredrik Wahlgren wrote:
     
    >like
    >

    >makes
    >

    >
    >
    >
    >[/ref]

    Tom Guest

  6. #6

    Default Re: 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

    Tom Moreau wrote:
     

    Steve Guest

  7. #7

    Default Re: 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


    "Steve Kass" <edu> wrote in message news:phx.gbl...
    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

    Tom Moreau wrote:
     


    Tom Guest

  8. #8

    Default Re: 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

    Tom Moreau wrote:
     

    Steve Guest

  9. #9

    Default Re: 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


    "Steve Kass" <edu> wrote in message news:phx.gbl...
    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

    Tom Moreau wrote:
     


    Tom Guest

  10. #10

    Default Re: Tricky query for me, should be simple for you

    Tom Guest

Similar Threads

  1. Simple query, very low performance
    By Bernhard Kornberger in forum MySQL
    Replies: 3
    Last Post: July 6th, 12:27 PM
  2. simple (?) query--help
    By Bosconian in forum MySQL
    Replies: 2
    Last Post: December 20th, 12:43 AM
  3. (simple?) query dilemma--help please
    By Bosconian in forum MySQL
    Replies: 2
    Last Post: November 19th, 02:00 AM
  4. A tricky query problem
    By quiero mas in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 12th, 07:17 AM
  5. simple query....
    By rodger in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: July 2nd, 01:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139