Select the minimum value for each row join by another table

Question

I have the following table:

           Table1                         Table2        
CardNo  ID   Record   Date          ID    Name      Dept    
1      101   8.00    11/7/2013      101   Danny     Green   
2      101   13.00   11/7/2013      102   Tanya     Red 
3      101   15.00   11/7/2013      103   Susan     Blue    
4      102   11.00   11/7/2013      104   Gordon    Blue    
5      103   12.00   11/7/2013                  
6      104   12.00   11/7/2013                  
7      104   18.00   11/7/2013                  
8      101    1.00   12/7/2013                  
9      101   10.00   12/7/2013                  
10     102    0.00   12/7/2013                  
11     102    1.00   12/7/2013                  
12     104    3.00   12/7/2013                  
13     104    4.00   12/7/2013  

i want the result to be like this:

Name    Dept    Record
Danny   Green   8.00
Tanya   Red     11.00
Susan   Blue    12.00
Gordon  Blue    18.00

where the result is only showing the minimum value of "Record" for each "Name", and filtered by the date selected. I'm using SQL.

Thorndike

select min(record) from table where date ='wateverdate' group by name? – Twister

Can you please double-check your example? And/or explain why the last line in your result table has 18.00 instead of 12.00? If you were to filter for 12/7/2013, would you expect values 1.00, 0.00, NULL, 3.00? – Delegate

Answer 1

Use:

select t2.Name, t2.Dept, min(t1.Record)
from table1 t1
join table2 t2 on t2.ID = t1.ID
group by t2.ID, t2.Name, t2.Dept

or

select t2.Name, t2.Dept, a.record
from table2 t2
join
(
    select t1.ID, min(t1.Record) [record]
    from table1 t1
    group by t1.ID
)a
on a.ID = t2.ID

For filtering add where clause, e.g.:

select t2.Name, t2.Dept, min(t1.Record)
from table1 t1
join table2 t2 on t2.ID = t1.ID
where t1.Date = '11/7/2013'
group by t2.ID, t2.Name, t2.Dept

Clavichord

Answer 2

Please try:

Select
    b.Name,
    b.Dept,
    MIN(Record) Record
from
    Table1 a join Table2 b on a.ID=b.ID
GROUP BY b.Name,
    b.Dept

Staphyloplasty

OP asked for MIN, not SUM. – Ichthyology

@Ichthyology Yeah! sorry. Thanks – Staphyloplasty

Kirill, could you possible add some more syntax behind to select only the 'date' I want? example: where date = '11/7/2013' – Thorndike

Answer 3

select 
        b.Name,
        b.Dept,
        min(a.Record)
from Table1 a,Table2 b 
where
        a.ID=b.ID 
group by 
        a.Record

Inveterate

The group by contains the columns that are NOT in aggregate functions, so: group by b.Name, b.Dept – Ichthyology