CODEWITHSUNDEEP
sqloracle-databasegroup-by
JimmyKowalski
JimmyKowalski

Reputation: 25

Select minimum row of another table

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!

Upvotes: 2

Views: 148

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

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;

Upvotes: 0

crumble
crumble

Reputation: 66

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

Upvotes: 0

dbajtr
dbajtr

Reputation: 2044

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

Upvotes: 2

Related Questions