Post reply

SELECT MAX from SubQuery

  • January 15, 2004 at 1:29 am

    #177834

    How can I perform the following in a stored procedure?

    From the table Results:

    ResultID integer (PK), StudentID integer (FK), Grade Char(1)

    Select the maximun number of grades a student has.

    e.g.

    1,1,D

    2,2,B

    3,1,D

    4,3,A

    5,1,B

    Will give 3 (Student 1 has 3 grades D,D & B)

    I want to execute something like

    SELECT MAX(GradeCount) FROM (SELECT StudentID, COUNT(Grade) AS GradeCount FROM Results GROUP BY StudentID)

    but, of course, that doesn't work.


    Shandy

  • January 15, 2004 at 1:55 am

    #490150

    would

    SELECT TOP 1 StudentID, Count(*) AS No_of_Grades FROM Results GROUP BY StudentID ORDER BY No_of_Grades DESC

    help

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 15, 2004 at 2:08 am

    #490151

    Thanks for the reply but unfortunatley I don't think so  as this was the way I tried to do it.

    My fault, I should have explained a bit more about what I wanted to do

    I actually want to return the value of MAX(..) into a variable like

    SELECT TOP 1 StudentID, @GradeCount = Count(*) AS No_of_Grades FROM Results GROUP BY StudentID ORDER BY No_of_Grades DESC

    but when I tried to do this I got the error message.

    A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.


    Shandy

  • January 15, 2004 at 2:24 am

    #490153

    declare @a int

    declare @GradesCount int

    SELECT TOP 1 @a = StudentID, @GradeCount = Count(*) FROM Results GROUP BY StudentID ORDER BY Count(*) DESC

    print @a + '  -  ' + @GradesCount

     

    should work

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 15, 2004 at 2:27 am

    #490154

    sorry, replace print.... by select @a, @GradesCount

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 15, 2004 at 2:33 am

    #490157

    Frank,

      that's exactly what I was after It's amazing how sometimes I simply cannot see the wood for the trees


    Shandy

  • January 19, 2004 at 6:32 am

    #490648

    An even easier solution:

    create view v_max (number) as

    select count(grade) number

    from results group by studentid

    select max(number) from v_max

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply