Professional Web Applications Themes

question with Highest occurrences - Microsoft SQL / MS SQL Server

Have a table: 1 = Good , 2 = mild and 3 = Bad Country city weather A AB 1 A CD 2 A EF 3 B AABB 1 And So on. Need for each country, the 1st and 2nd highest %Good weather City. sql or crystal report way. -- Sent by msg_2222 from yahoo in field com This is a spam protected message. Please answer with reference header. Posted via http://www.usenet-replayer.com/cgi/content/new...

  1. #1

    Default question with Highest occurrences

    Have a table:
    1 = Good , 2 = mild and 3 = Bad
    Country city weather
    A AB 1
    A CD 2
    A EF 3
    B AABB 1
    And So on.

    Need for each country, the 1st and 2nd highest %Good
    weather City. sql or crystal report way.





    --
    Sent by msg_2222 from yahoo in field com
    This is a spam protected message. Please answer with reference header.
    Posted via http://www.usenet-replayer.com/cgi/content/new
    msg_2222@yahoo.com Guest

  2. #2

    Default Re: question with Highest occurrences

    Try

    create table CountryCityWeather(Country char(1),city varchar(4),weather
    tinyint)

    INSERT CountryCityWeather values('A','AB',1)
    INSERT CountryCityWeather values('A','CD',2)
    INSERT CountryCityWeather values('A','EF',3)
    INSERT CountryCityWeather values('B','AABB',1)

    Select * from CountryCityWeather a where weather in
    ( select TOP 2 weather from CountryCityWeather b where a.country = b.country
    order by weather)



    HTH,
    Srinivas Sampangi

    "com" <ip-mobilphone.net> wrote in
    message news:cvx.algx.net... 


    sampangi Guest

  3. #3

    Default Re: question with Highest occurrences

    .... order by weather desc

    to get the highest occurrences?

    SK

    sampangi wrote:
     
    >
    >
    >
    >[/ref]

    Steve Guest

  4. #4

    Default Re: question with Highest occurrences

    If the expected output is

    Country city weather
    ------- ---- -------
    A CD 2
    A EF 3
    B AABB 1

    We should be writing as

    Select * from CountryCityWeather a where weather in
    ( select TOP 2 weather from CountryCityWeather b where a.country = b.country
    order by weather DESC )

    Thanx,
    Srinivas Sampangi


    "Steve Kass" <edu> wrote in message
    news:phx.gbl... [/ref]
    b.country 
    > >
    > >
    > >
    > >[/ref]
    >[/ref]


    sampangi Guest

  5. #5

    Default Re: Re: question with Highest occurrences

    Dear Steve Kass ,

    In your posting Re: question with Highest occurrences from Wed, 13 Aug
    2003 04:17:51 -0400 you write:
     
    > >
    > >
    > >
    > >[/ref]
    >[/ref]


    My sql procedure results in:

    country city

    A AB
    A Ac
    A AD
    A AE
    B BA
    B BB
    B BC
    C CA
    C CB
    C CC

    Now, Need to count city per each country.
    and get an output like:

    country Num Of city
    A 4
    B 3
    C 3


    sql/crystal report9.








    --
    Spam protected message from:
    Sent by msg_2222 from yahoo subpart from com
    Posted via http://www.usenet-replayer.com/cgi/content/new
    msg_2222@yahoo.com Guest

Similar Threads

  1. Highest Distinct Value
    By mslima in forum Coldfusion Database Access
    Replies: 4
    Last Post: April 19th, 01:42 PM
  2. Getting amount of Occurrences
    By Vbprog40 in forum Macromedia ColdFusion
    Replies: 5
    Last Post: April 18th, 03:04 PM
  3. Strange memory occurrences with MX on XP
    By Flashster in forum Macromedia Director Basics
    Replies: 2
    Last Post: January 17th, 01:54 AM
  4. Replies: 3
    Last Post: October 15th, 09:06 AM
  5. How get number of occurrences in a variable?
    By malcolm k in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 30th, 03:59 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