[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Min() select list not contained

Posted on 2011-10-24
14
Medium Priority
?
355 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:vrosas_03
  • 5
  • 5
  • 3
  • +1
14 Comments
 
LVL 23

Expert Comment

by:Saqib Khan
ID: 37020129
SELECT Min(grade) AS grade, student_name, test_name FROM student_work
GROUP BY student_name,test_name;

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37020158
The error tells you why:

To explain:  You need to group by all non-aggregate columns in the query.
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 37020177
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:vrosas_03
ID: 37020371
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37020419
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 37020453
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
 
LVL 61

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 37020459
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
 

Author Comment

by:vrosas_03
ID: 37020469
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37020489
If the row_number call above doesn't work:  What database?
0
 

Author Closing Comment

by:vrosas_03
ID: 37020494
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
 

Author Comment

by:vrosas_03
ID: 37020790
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37021108
This was posted in MySQL and MS SQL.  Two different database platforms with different options.

SQLServer 2008 was what I was after.
0
 

Author Comment

by:vrosas_03
ID: 37021427
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37021439
The issue with that and even my proposed solution is it hits the table twice.

Row_number only once.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question