Select top 5 in each group


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Select top 5 in each group

  1. #1
    Edgar Guest

    Select top 5 in each group


    Dear All,

    I have a table which included the following field:

    name(string)
    class(string)
    score(integer)

    Is it possible to select the top 5 records in each class by score and show
    them in one table? Thanks

  2. #2
    Bob Rouse Guest

    Re: Select top 5 in each group


    This may be a bit cumbersome, but should work (assuming SQL-Server, table
    name of Grades):

    Declare @Class varchar(32)
    Declare cClass CURSOR for
    Select distinct class from Grades
    Open cClass
    Fetch NEXT from cClass into @Class
    While (@@FETCH_STATUS = 0)
    BEGIN
    Select top 5 * from grades where Class = @Class order by score desc
    Fetch NEXT from cClass into @Class
    END
    close cClass
    deallocate cClass


    "Edgar" <edgar_chan@email.com> wrote:
    >
    >Dear All,
    >
    >I have a table which included the following field:
    >
    >name(string)
    >class(string)
    >score(integer)
    >
    >Is it possible to select the top 5 records in each class by score and show
    >them in one table? Thanks



  3. #3
    David Satz Guest

    Re: Select top 5 in each group

    check out this site I think there c/b an ex.:
    http://www.umachandar.com/technical/...ripts/main.htm
    "Edgar" <edgar_chan@email.com> wrote in message
    news:3c7de65e$1@10.1.10.29...
    >
    > Dear All,
    >
    > I have a table which included the following field:
    >
    > name(string)
    > class(string)
    > score(integer)
    >
    > Is it possible to select the top 5 records in each class by score and show
    > them in one table? Thanks




  4. #4
    Joe \Nuke Me Xemu\ Foster Guest

    Re: Select top 5 in each group

    "Edgar" <edgar_chan@email.com> wrote in message <news:3c7de65e$1@10.1.10.29>...

    > I have a table which included the following field:
    >
    > name(string)
    > class(string)
    > score(integer)
    >
    > Is it possible to select the top 5 records in each class by score and show
    > them in one table? Thanks


    Sounds kinda familiar...

    http://groups.google.com/groups?selm...%40tkmsftngp04

    If your RDBMS supports TOP, try this as well:

    http://groups.google.com/groups?selm...%40tkmsftngp05

    --
    Joe Foster <mailto:jlfoster%40znet.com> Got Thetans? <http://www.xenu.net/>
    WARNING: I cannot be held responsible for the above They're coming to
    because my cats have apparently learned to type. take me away, ha ha!



  5. #5
    Edgar Guest

    Re: Select top 5 in each group


    Thank you very much for all of your help, Bob, David and Joe.
    I will try your solutions.

    "Edgar" <edgar_chan@email.com> wrote:
    >
    >Dear All,
    >
    >I have a table which included the following field:
    >
    >name(string)
    >class(string)
    >score(integer)
    >
    >Is it possible to select the top 5 records in each class by score and show
    >them in one table? Thanks



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center