This website requires Javascript to function properly. Please go to the setting of your web browser and enable Javascript for this website.

Ad by

予人玫瑰，手有余香：加拿大新天地工作移民诚聘求职顾问&行业导师！

Ad by

予人玫瑰，手有余香：加拿大新天地工作移民诚聘求职顾问&行业导师！

Oracle two table join

iamlooking(quiet)

I have two Oracle tables

first:
firstid: name
01 A
02 B
03 C
04 D
05 E

second:
secondid firstid date
01 01 01/01/1999
02 01 02/30/1998
03 01 07/29/2002
04 02 10/11/2002
05 02 11/01/2001
06 03 08/07/2000
07 03 03/09/2001

I would like to get a selection like this

firstid name date
01 A (the only one,the earlest date in the second table)
02 B
03 C
04 D

Thanks so much for any help.

(#718293@0)
Last Updated: 2002-8-28
This post has been archived. It cannot be replied.

Report

Replies, comments and Discussions:

枫下家园 / 电脑用户 / Oracle two table join -iamlooking(quiet); 2002-8-28 {820} (#718293@0)
I have two Oracle tables

first:
firstid: name
01 A
02 B
03 C
04 D
05 E

second:
secondid firstid date
01 01 01/01/1999
02 01 02/30/1998
03 01 07/29/2002
04 02 10/11/2002
05 02 11/01/2001
06 03 08/07/2000
07 03 03/09/2001

I would like to get a selection like this

firstid name date
01 A (the only one,the earlest date in the second table)
02 B
03 C
04 D

Thanks so much for any help.

try : select a.firstid, a.name, min(b.date) from first a, second b where a.firstid = b.fistid group by a.firstid, a.name -cloud2001(卷云溶月); 2002-8-28 (#718310@0)

Thanks so much for your kind help, But there is an error "ORA-00937: not a single-group group function" Thanks -iamlooking(quiet); 2002-8-28 (#718336@0)
Sorry, it should be work, that is my fault, Again thanks for your help -iamlooking(quiet); 2002-8-28 (#718340@0)

try this.... -red7male(洪七公); 2002-8-28 {143} (#718346@0)
select
a.firstid,
a.name,
min(b.date)
From
first a
left join
second b
on
a.firstid=b.firstid
group by
a.firstid,
a.name
Thanks all for your kind help, after couple hours working on this, I still can not get the right selection Thanks again -iamlooking(quiet); 2002-8-28 (#718387@0)
问题不清楚，能在进一步说明吗？ -xiaozhao(bird); 2002-8-31 (#723245@0)
IF YOU ARE USING ORACLE 9I --- IT CAN WORK WITH Scalar Subquery -arthurxu(arthur); 2002-9-1 {460} (#724017@0)
SQL> select firstid, name, (
2 select min(D) from second
3 where secondid= first.firstid )
4 from first
5 ;

FIRSTID NAME (SELECTMIN(D)FROMSECONDWHERESE
---------- ---------- ------------------------------
1 A 2002-8-30 18:16:33
2 B 2002-8-30 18:16:53
3 C 2002-8-30 18:17:05
4 D
5 E
6 F

6 rows selected

SQL>

second solution with ORACLE 9i -arthurxu(ArthurXu OCP8,9i); 2002-9-1 {355} (#724028@0)
SQL> select a.firstid, a.name, b.d
2 from first a, second b
3 where a.firstid=b.firstid
4 and b.d= ( select min(d) from second where second.firstid = a.firstid)
5 ;

FIRSTID NAME D
---------- ---------- -----------
1 A 2002-9-1 18
2 B 2002-8-31 1
3 C 2002-8-30 1

SQL>

这个没有用到ORACLE9i的new feature -arthurxu(ArthurXu OCP8,9i); 2002-9-1 {376} (#724024@0)
SQL> select min(firstid), min(name), min(d) from
2 (
3 select a.firstid, a.name, b.d
4 from first a, second b
5 where a.firstid = b.firstid
6 )
7 group by firstid
8 ;

MIN(FIRSTID) MIN(NAME) MIN(D)
------------ ---------- -----------
1 A 2002-9-1 18
2 B 2002-8-31 1
3 C 2002-8-30 1
select f.id, f.name,(select min(s.date) from second as s where s.firstid=f.id) as date from first as f; -xiaozhao(bird); 2002-9-2 (#725021@0)
保守但是通用的解决方法：用子查询 -wanderca(bum); 2002-9-5 {109} (#729448@0)
SELECT firstid, name,
(SELECT MIN(date) FROM second WHERE second.firstid = first.firstid)
FROM first

@PEI

Oracle two table join

Replies, comments and Discussions:

More Topics