CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
Bro
Bro

Reputation: 57

Subqueries oracle

I would like to select house_id, house_name, image_name, and size for the lowest image size for each house. 

create table house( id int, name varchar(50));

create table image( id int, name varchar(50), house_id int, siz int);

insert into house (id, name) values (1,'house1');
insert into house (id, name) values (2,'house2');

insert into image values (31,'img1',1, 10);
insert into image values (32,'img2',2, 20);
insert into image values (33,'img3',2, 15);
insert into image values (34,'img4',2, 19);

http://sqlfiddle.com/#!4/f86fe/35

Can anybody help me? I tried to prepare some query, but in the last column I always get the null values.

select h.id, h.name, m.mid, i.name
from house h left join (select min(siz) as mid,house_id from image
group by house_id) m on h.id = m.house_id
left join image i on i.id=m.house_id

Results:

| ID |   NAME | MID |   NAME |
|----|--------|-----|--------|
|  1 | house1 |  10 | (null) |
|  2 | house2 |  15 | (null) |

Upvotes: 2

Views: 54

Answers (2)

Matthew McPeak
Matthew McPeak

Reputation: 17924

In Oracle 12c, you can use OUTER APPLY, which is simpler (in my view) than ROW_NUMBER()

SELECT h.id, h.name, i.siz, i.name as image_name
FROM   house h
OUTER APPLY ( SELECT i.*
              FROM   image i
              WHERE  i.house_id = h.id
              ORDER BY i.siz ASC
              FETCH FIRST 1 ROW ONLY ) i;

+----+--------+-----+------------+
| ID |  NAME  | SIZ | IMAGE_NAME |
+----+--------+-----+------------+
|  1 | house1 |  10 | img1       |
|  2 | house2 |  15 | img3       |
+----+--------+-----+------------+

Upvotes: 0

MT0
MT0

Reputation: 167962

Two options - one using ROW_NUMBER() analytic function and the other using aggregation functions:

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table house( id int, name varchar(50));

create table image( id int, name varchar(50), house_id int, siz int);

insert into house (id, name) values (1,'house1');
insert into house (id, name) values (2,'house2');

insert into image values (31,'img1',1, 10);
insert into image values (32,'img2',2, 20);
insert into image values (33,'img3',2, 15);
insert into image values (34,'img4',2, 19);

Query 1:

SELECT h.id, h.name, i.siz, i.name AS image_name
FROM   house h
       LEFT OUTER JOIN (
         SELECT i.*,
                ROW_NUMBER() OVER (
                  PARTITION BY house_id
                  ORDER BY     siz
                ) AS rn
         FROM   image i
       ) i
       ON ( h.id = i.house_id AND i.rn = 1 )

Results:

| ID |   NAME | SIZ | IMAGE_NAME |
|----|--------|-----|------------|
|  1 | house1 |  10 |       img1 |
|  2 | house2 |  15 |       img3 |

Query 2:

SELECT h.id,
       MIN( h.name ) AS name,
       MIN( i.siz ) AS siz,
       MIN( i.name ) KEEP ( DENSE_RANK FIRST ORDER BY siz ) AS image_name
FROM   house h
       LEFT OUTER JOIN image i
       ON ( h.id = i.house_id )
GROUP BY h.id

Results:

| ID |   NAME | SIZ | IMAGE_NAME |
|----|--------|-----|------------|
|  1 | house1 |  10 |       img1 |
|  2 | house2 |  15 |       img3 |

Upvotes: 3

Related Questions