OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » SQL help

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL help [message #9855]

Tue, 09 December 2003 08:58

dana
Messages: 7
Registered: February 2001

Junior Member

I need to extract just one occurrance of the ID, does not matter which one, and key fields from a table and I am using the key from another table.

Here is the code and the result;

select calcid, mkey
from bencalc
where bencalc.mkey IN (select empdatav.mkey from empdatav)
;

ID Key
43224 000009982
44308 000009982
45521 000009982
47108 000009982
48406 000009982
43373 000009983
44151 000009983
44152 000009983
44153 000009983
45522 000009983

I would like the result to be

ID Key
48406 000009982
45522 000009983

Thanks Dana

Report message to a moderator

Re: SQL help [message #9857 is a reply to message #9855]

Tue, 09 December 2003 10:06

sverch
Messages: 582
Registered: December 2000

Senior Member

You can do it in PL/SQL:

SQL> SELECT*FROM V;

ID KEY
--------- ---------
43224 9982
44308 9982
45521 9982
47108 9982
48406 9982
43373 9983
44151 9983
44152 9983
44153 9983
45522 9983

10 rows selected.

SQL> DECLARE
2
3 intId INTEGER;
4
5
6 BEGIN
7
8 FOR r IN (SELECT distinct key FROM v)
9 LOOP
10
11 SELECT id INTO intid FROM v
12 WHERE key=r.key AND rownum<2;
13
14 DBMS_OUTPUT.PUT_LINE (intid||' '||r.key);
15
16 END LOOP;
17
18 END;
19 /
43224 9982
43373 9983

Report message to a moderator

Re: SQL help [message #9858 is a reply to message #9855]

Tue, 09 December 2003 10:13

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Dana,

Try

SELECT bc.calcid
,      bc.mkey
FROM   bencalc    bc
WHERE  bc.mkey IN (SELECT empdatav.mkey FROM empdatav)
AND    bc.calcid = (SELECT MAX(bc2.calcid) FROM bencalc bc2 WHERE bc2.mkey = bc.mkey)
;

HTH,

A.

Report message to a moderator

Re: SQL help [message #9859 is a reply to message #9855]

Tue, 09 December 2003 10:18

sverch
Messages: 582
Registered: December 2000

Senior Member

Sorry, this is much simplier than how it first came to my mind:

QL> select key, max(id) from v
2 group by key;

KEY MAX(ID)
-------- ---------
9982 48406
9983 45522

You can do any subqueries and change min to max.

Report message to a moderator

Previous Topic:	Prefixed Vs Non-Prefixed Indexes
Next Topic:	SQL that fills TEMP tablespace

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 15:12:37 CDT 2024