SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select statement and MIN() help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Prosercunus
Starting Member

USA
22 Posts

Posted - 10/09/2012 :  18:18:43  Show Profile  Reply with Quote
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.

Edited by - Prosercunus on 10/09/2012 18:37:45

chadmat
The Chadinator

USA
1974 Posts

Posted - 10/09/2012 :  19:10:54  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/09/2012 :  21:23:44  Show Profile  Reply with Quote


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


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

Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 10/10/2012 :  01:18:04  Show Profile  Visit chadmat's Homepage  Reply with Quote
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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/10/2012 :  01:58:02  Show Profile  Reply with Quote
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/

Go to Top of Page

Prosercunus
Starting Member

USA
22 Posts

Posted - 10/10/2012 :  04:08:17  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/10/2012 :  23:50:41  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000