CODEWITHSUNDEEP
sqlmaxmin
macbb
macbb

Reputation: 49

SQL Min and Max for multiple rows

I have a table with values like:

Name  Test  Score
Bob   Test1 97.0
Bob   Test2 96.5
Bob   Test3 94.5
Joe   Test1 96.5
Joe   Test2 98.5
Joe   Test3 92.0

I would like the MIN and MAX score for each Name on one row, like:

Name  Min_Score  Max_Score
Bob   94.5       97.0
Joe   92.0       98.5

I can get the MIN or MAX by itself, but not sure how to get both at the same time.

Upvotes: 2

Views: 8389

Answers (2)

user2560539
user2560539

Reputation:

You have to use group by on user name

select distinct tname, 
min(tscore) as min_score, 
max(tscore) as max_score 
from test_table 
group by tname;

http://sqlfiddle.com/#!9/302fd3/1 , http://ideone.com/YmHA7x

Upvotes: 0

EoinS
EoinS

Reputation: 5482

You can group your selection by name. This will provide you with each unique name. You can also use aggregate functions min and max that will scan your table for each unique name. This is a common request:

SELECT 
  name
  , min(score) as Min_score
  , max(score) as max_score 
FROM table 
GROUP by name

Here is an example of the query and the correct results

Upvotes: 4

Related Questions