Min() select list not contained

how do I include a third column into this table without changing the number of rows?

SELECT Min(grade) AS grade, student_name FROM student_work
GROUP BY student_name;

grade student name
34      Candy Nelson
34      Eric Carter
23      Melody Johnson

What I want is this:

grade       student_name       test_name
34      Candy Nelson                test 5
34      Eric Carter                     test 3
23      Melody Johnson            test 1

I tried this but it didn't work

SELECT Min(grade) AS grade, student_name, test_name FROM student_work
GROUP BY student_name;


Msg 8120, Level 16, State 1, Line 1
Column 'student_work.test_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

vrosas_03Asked:
Who is Participating?
 
HainKurtSr. System AnalystCommented:
oops, you want min

similar query, just change order

select * from (
select *, row_number() over (partition by student_id order by grade ASC) rn
from student_work
) x where rn=1
0
 
Saqib KhanSenior DeveloperCommented:
SELECT Min(grade) AS grade, student_name, test_name FROM student_work
GROUP BY student_name,test_name;

0
 
slightwv (䄆 Netminder) Commented:
The error tells you why:

To explain:  You need to group by all non-aggregate columns in the query.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
HainKurtSr. System AnalystCommented:
if your student_work table has "test_name" column, and it is same for all records for same student, then above post will work...

post a sample data from your table, so we can see what is going on...
0
 
vrosas_03Author Commented:
Here is the sample data.  It is just one table.  

I had tried

SELECT Min(grade) AS grade, student_name, test_name FROM student_work
GROUP BY student_name,test_name;

But it prints out extra rows and looks ugly.  Its not what I wanted

This is what I want:
I want to just see the test that they took which gave them the grade and that's all:
34      Candy Nelson            Test 5
34      Eric Carter                  Test 5
23      MELODY JOHNSON   Test 1


SELECT * FROM student_work

student_id student_name test_name  grade
1	MELODY JOHNSON	TEST 1	23
1	MELODY JOHNSON	TEST 2	83
1	MELODY JOHNSON	TEST 3	43
1	MELODY JOHNSON	TEST 4	93
1	MELODY JOHNSON	TEST 5	63
2	Candy Nelson	TEST 1	53
2	Candy Nelson	TEST 2	73
2	Candy Nelson	TEST 3	55
2	Candy Nelson	TEST 4	49
2	Candy Nelson	TEST 5	34
3	Eric Carter	TEST 1	53
3	Eric Carter	TEST 2	73
3	Eric Carter	TEST 3	55
3	Eric Carter	TEST 4	49
3	Eric Carter	TEST 5	34

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
This was cross posted in two different database zones
but try something like:

select grade, student_name, test_name,  grade
from student_work where (grade, student_name) in (
SELECT Min(grade), student_name FROM student_work
GROUP BY student_name
);
0
 
HainKurtSr. System AnalystCommented:
look at this

student_id student_name test_name  grade
1      MELODY JOHNSON      TEST 1      23
1      MELODY JOHNSON      TEST 2      83
1      MELODY JOHNSON      TEST 3      43
1      MELODY JOHNSON      TEST 4      93
1      MELODY JOHNSON      TEST 5      63

for student 1, what do you want to see?
oops, you want the record with max grade :)

here it is:

select * from (
select *, row_number() over (partition by student_id order by grade desc) rn
from student_work
) x where rn=1
0
 
vrosas_03Author Commented:
select grade, student_name, test_name,  grade
from student_work where (grade, student_name) in (
SELECT Min(grade), student_name FROM student_work
GROUP BY student_name
);

trying the above I get this error:

Msg 4145, Level 15, State 1, Line 2
An expression of non-boolean type specified in a context where a condition is expected, near ','.
0
 
slightwv (䄆 Netminder) Commented:
If the row_number call above doesn't work:  What database?
0
 
vrosas_03Author Commented:
Thanks,
select * from (
select *, row_number() over (partition by student_id order by grade ASC) rn
from student_work
) x where rn=1

Seems to work
0
 
vrosas_03Author Commented:
the database name is school_db.  

I'm using sqlserver 2008 R2.   express edition   Management Studio

I'm not sure what you meant by what database.  

It would be nice though if there were a simpler solution that worked.
0
 
slightwv (䄆 Netminder) Commented:
This was posted in MySQL and MS SQL.  Two different database platforms with different options.

SQLServer 2008 was what I was after.
0
 
vrosas_03Author Commented:
Here's a solution i found at this website:
http://weblogs.sqlteam.com/jeffs/archive/2007/03/28/60146.aspx

select b.*
from
student_work b
inner join
(    select student_name, Min(grade) as MinGrade
    from student_work
    group by student_name
) m
on b.student_name = m.student_name and b.grade = m.MinGrade
0
 
slightwv (䄆 Netminder) Commented:
The issue with that and even my proposed solution is it hits the table twice.

Row_number only once.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.