This topic has been archived. It cannot be replied.
-
枫下家园 / 电脑用户 / Oracle two table join
-iamlooking(quiet);
2002-8-28
{820}
(#718293@0)
-
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....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
-red7male(洪七公);
2002-8-28
{143}
(#718346@0)
-
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 SubquerySQL> 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>
-arthurxu(arthur);
2002-9-1
{460}
(#724017@0)
-
second solution with ORACLE 9iSQL> 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>
-arthurxu(ArthurXu OCP8,9i);
2002-9-1
{355}
(#724028@0)
-
这个 没有用到ORACLE9i的new featureSQL> 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
-arthurxu(ArthurXu OCP8,9i);
2002-9-1
{376}
(#724024@0)
-
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)
-
保守但是通用的解决方法:用子查询SELECT firstid, name,
(SELECT MIN(date) FROM second WHERE second.firstid = first.firstid)
FROM first
-wanderca(bum);
2002-9-5
{109}
(#729448@0)