Select minimum row of another table

Question

In addition to this question I want to select an additional row.

I have the following tables:

          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    Date
Danny   Green   8.00      12/7/2013
Tanya   Red     11.00     11/7/2013
Susan   Blue    12.00     11/7/2013
Gordon  Blue    18.00     12/7/2013

I get an group by error with following select:

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

If I replace t1.Date with min(t1.Date) it looks fine, but have the problem that not the corresponding Date of the record is selected. I would be glad if you could help me!

I don't understand your expected output. Do you want the minimum date or the minimum record? — Tim Biegeleisen, Aug 29 '17 at 11:24
I want to get the minimum Record with the corresponding date. If am following dbajtr approach, I get two rows as a result, but need only one. — JimmyKowalski, Aug 29 '17 at 11:38

score 2 · Answer 1 · answered Aug 29 '17 at 11:10

2

You are grouping by different columns than you have selected, try this:

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

answered Aug 29 '17 at 11:10

dbajtr

2,024
2
14
22

Tim Biegeleisen · Accepted Answer · 2017-08-29T12:56:14.547

0

Here is an option using RANK:

SELECT
    t.Name,
    t.Dept,
    t.Record,
    t.Date
FROM
(
    SELECT t1.*, t2.*,
        RANK() OVER (PARTITION BY t2.ID ORDER BY t2.Record) rank
    FROM Table2 t2
    INNER JOIN Table1 t1
        ON t1.ID = t2.ID
) t
WHERE t.rank = 1;

edited Aug 29 '17 at 12:56

answered Aug 29 '17 at 11:17

Tim Biegeleisen

502,043
27
286
360

I followed your approach, but used rank() instead and got a correct result. Thank you! – JimmyKowalski Aug 29 '17 at 12:52

score 0 · Answer 3 · answered Aug 29 '17 at 11:43

You have to ensure two things. Each non-aggregated column in the result set must occure in the group by. Now you can have only one name dept combination in your result set. So you have to ensure that there is only one row of t1. Normaly you will choose the row which contais the first date the record was broken:

  select t2.name,
         t2.dept,
         min(t1.record) as record,
         min(t1.date)   as date
    from t1
    join t2 on t2.id = t1.id
group by t1.id, t2.name, t2.dept

But this select is giving me the wrong result when the results of `min(t1.record)` and `min(t1.date)` don't have the same row number. — JimmyKowalski, Aug 29 '17 at 11:50

Select minimum row of another table

3 Answers3