Results 1 to 8 of 8

Thread: sql query

  1. #1
    Join Date
    Jul 2009
    Posts
    3

    Unanswered: sql query

    Hi, I have a student table with stdno,stdname,stdmajor,stdgpa etc as columns.
    I need to create a Sql statement to find : For each major ----the stdno,name and gpa of student with highest gpa.

    The following stmt gives error,pls help
    SELECT StdMajor,StdNo,StdFirstName,StdLastName,StdGPA
    WHERE EXISTS
    (Select StdMajor,MAX(stdgpa) from student
    GROUP BY Stdmajor)
    GROUP BY Stdmajor,,StdNo,StdFirstName,StdLastName,StdGPA

    Rosh

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rosh
    The following stmt gives error,pls help
    i'd be happy to

    - the outer query is missing a FROM clause, which is a syntax error (except that it's not a syntax error in SQL Server)

    - the subquery will return at least one row provided that the table contains at least one row, or, in other words, the EXISTS will always be true if the table is not empty, and we can rightfully assume that the table is not empty, au moins -- so this is a semantic error, but it's not critical, as it only wastes resources

    - the GROUP BY in the outer query has a missing element as indicated by the consecutive commas, and this is a syntax error

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    3
    hi,

    sorry for my typing error.my query is

    SELECT StdMajor,StdNo,StdFirstName,StdLastName,StdGPA
    From Student
    WHERE EXISTS
    (Select StdMajor,MAX(stdgpa) from student
    GROUP BY Stdmajor)
    GROUP BY Stdmajor,StdNo,StdFirstName,StdLastName,StdGPA

    but it lists all students with all majors.doesnt group by stdmajor

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You aren't filtering the rows with a WHERE clause, so all rows in the table are processed. You are grouping on many columns, so each group contains all of the rows with the same values for the grouping columns (which probably means that there is only one row in each group).

    SQL is almost certainly doing what you told it to do, but probably not what you want. Please explain what you want it to do in English, not code and I'm sure that someone here can help you get it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jul 2009
    Posts
    3
    Hi,

    I am actually new to sql
    I have a student table with stdno,std(first,last)name,stdmajor,stdgpa etc as columns.
    I need to create a Sql statement to find : For each major ----the stdno,name and gpa of student with highest gpa.
    I want to do the above using subquery

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What have you covered so far in class? Is this assignment already overdue. Based on where most schools are at this point in summer session, I'm confused by this question.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    What have you covered so far in class?
    you're so jaded

    what made you think this couild possibly be a school assignment?

    Quote Originally Posted by Pat Phelan
    Based on where most schools are at this point in summer session, I'm confused by this question.
    that's your northern hemispherism showing again

    this happens to be week 1 of term 3 in australia, the week after winter break, when the serious coursework commences
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    Please explain what you want it to do in English
    that was given in post #1 -- "For each major, give the stdno, name and gpa of the student with highest gpa."

    a classic SQL problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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