Select statement and MIN() help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select statement and MIN() help

Author

Topic

Prosercunus
Starting Member

22 Posts

Posted - 2012-10-09 : 18:18:43

I have a student table with a sname (Student Name) and a gpa (GPA) columns.

I need to sort or find the lowest GPA of the bunch so I...

SELECT sname, MIN(gpa) as "Lowest GPA" FROM Student
Group By sname

However it should only be showing the one student with a 0.00 GPA, but instead it is showing all of the students and all of the GPA

I also can never do MIN(gpa) or AVG(GPA) since GPA is not set as an Integer and does not work as such.

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-09 : 19:10:54

You are not asking for a single student with the Min GPA, you are asking for the Student in each group who has the Min GPA. If you want the single lowest GPA you need:

SELECT sname, gpa as "Lowest GPA"
FROM Student
WHERE gpa = (SELECT MIN(gpa) FROM Student)

-Chad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-09 : 21:23:44


SELECT TOP 1 WITH TIES sname,gpa
FROM table
ORDER BY gpa ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-10 : 01:18:04

quote:
Originally posted by visakh16

SELECT TOP 1 WITH TIES sname,gpa
FROM table
ORDER BY gpa ASC

Ahh, even better
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-10 : 01:58:02

quote:
Originally posted by chadmat

quote:
Originally posted by visakh16

SELECT TOP 1 WITH TIES sname,gpa
FROM table
ORDER BY gpa ASC

Ahh, even better
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tnx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Prosercunus
Starting Member

22 Posts

Posted - 2012-10-10 : 04:08:17

You guys are great. I see exactly how that is done.

I tried doing this same thing with AVG and it didn't exactly work out as well. some further digging it seems newer SQL server doesn't let you do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-10 : 23:50:41

do you mean this?


SELECT TOP 1 WITH TIES sname,AVG(gpa) AS avggpa
FROM table
GROUP BY sname
ORDER BY avggpa ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources