Select top 5 in each group

[Edgar]

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

[Bob Rouse]

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

[David Satz]

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

[Joe \Nuke Me Xemu\ Foster]

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

[Edgar]

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