Japanese

Hi,

within a logging table (T1)  I'm looking for

a) the youngest row for each "name"

b) those names without any entry in the logging table. Possible names are defined in table T2.

I have a solution (see below), but I believe there must be a more sufficient one.

Any help is appreciated.

Thanks, Hans

These are the table definitions;

CREATE TABLE "T1" ( "NAME" VARCHAR2(20 BYTE), "STATUS" VARCHAR2(20 BYTE), "LOG_DATE" DATE, "INFO" VARCHAR2(20 BYTE) );

CREATE TABLE "T2" ("NAME" VARCHAR2(20 BYTE) ) ;

And some test values

Insert into T1 (NAME,STATUS,LOG_DATE,INFO) values ('A','0',to_date('14.12.2015 12:09:51','DD.MM.YYYY HH24:MI:SS'),'INFO A 0');

Insert into T1 (NAME,STATUS,LOG_DATE,INFO) values ('A','1',to_date('14.12.2015 12:10:16','DD.MM.YYYY HH24:MI:SS'),'INFO A 1');

Insert into T1 (NAME,STATUS,LOG_DATE,INFO) values ('B','1',to_date('14.12.2015 12:10:38','DD.MM.YYYY HH24:MI:SS'),'INFO B 1');

Insert into T1 (NAME,STATUS,LOG_DATE,INFO) values ('B','0',to_date('14.12.2015 12:11:00','DD.MM.YYYY HH24:MI:SS'),'INFO B 0');

Insert into T2 (NAME) values ('A');

Insert into T2 (NAME) values ('B');

Insert into T2 (NAME) values ('C');

My solution:

with a as (

select t1.name, max(log_date) MaxLogDate from T1 group by name

),

b as (

select t1.name, t1.status, t1.log_date, t1.info

from

a, t1

where

t1.name=a.name and

t1.log_date = a.MaxLogDate)

select t2.name, b.log_date, b.status

from b right outer join t2 on t2.NAME = b.name

order by b.name;

Giving these results: