OraFAQ Forum: SQL & PL/SQL » Selecting a row based on the existence of another

Home » SQL & PL/SQL » SQL & PL/SQL » Selecting a row based on the existence of another

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

Selecting a row based on the existence of another [message #219141]

Tue, 13 February 2007 02:49

matpj
Messages: 115
Registered: March 2006
Location: London, UK

Senior Member

Hi all,

I have a table that could contain the following info.

Project ID Document Actuals
P00001 D1 50
P00001 D2 30
P00002 D1 25
P00003 D1 40
P00004 D1 10
P00004 D2 20

I need to somehow examine the table and if a project exists with both D1 and D2 documents then I should only return D2
If a project only has D1 then I should return that.
A project will never have only a D2 document.

so it is either D1, or D1 AND D2 - in which case I would pick D2

can anyone suggest how I might do this, as I am totally stumped...

thanks in advance,
Matt

Report message to a moderator

Re: Selecting a row based on the existence of another [message #219172 is a reply to message #219141]

Tue, 13 February 2007 08:43

pmaupoil
Messages: 40
Registered: February 2007
Location: France

Member

Hopefully this will suit your expectations...

SELECT *
FROM (SELECT project_id,
document,
actuals,
row_number() over(PARTITION BY project_id
ORDER BY document DESC) rown
FROM tb)
WHERE rown = 1;

Report message to a moderator

Re: Selecting a row based on the existence of another [message #219175 is a reply to message #219141]

Tue, 13 February 2007 08:58

matpj
Messages: 115
Registered: March 2006
Location: London, UK

Senior Member

oooo, thats looking good.
I have never used those functions before.

here's a curve ball for you though.

lets take the example above.

in reality I have another document 'type' available.

Project_ID Document Actuals
P00001 D1 50
P00001 D2 30
P00001 S1 30
P00002 D1 25
P00002 S1 25
P00003 D1 40
P00004 D1 10
P00004 D2 20

in the past I have queried just the S1 figures in a seperate query.
DO you know if your SQL can be applied to always return the S1, and then the same rules for D1/D2?

thanks (I owe you a beer!),
Matt

Report message to a moderator

Re: Selecting a row based on the existence of another [message #219178 is a reply to message #219175]

Tue, 13 February 2007 09:04

pmaupoil
Messages: 40
Registered: February 2007
Location: France

Member

try that...

SELECT *
FROM (SELECT project_id,
document,
actuals,
row_number() over(PARTITION BY project_id, substr(document, 1, 1)
ORDER BY substr(document, -1) DESC) rown
FROM tb)
WHERE rown = 1;

Report message to a moderator

Re: Selecting a row based on the existence of another [message #219181 is a reply to message #219172]

Tue, 13 February 2007 09:12

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Another way of doing that would be:

create table test_project (Project_ID  varchar2(10), Document varchar2(2),  Actuals number);

insert into test_project values('P00001', 'D1', 50);
insert into test_project values('P00001', 'D2', 30);
insert into test_project values('P00002', 'D1', 25);
insert into test_project values('P00003', 'D1', 40);
insert into test_project values('P00004', 'D1', 10);
insert into test_project values('P00004', 'D2', 20);

QL> select distinct 
 2         project_id
 3        ,first_Value(document) over (partition by project_id order by decode(document,'D1',2,'D2',1,3)) d1_2
 4        ,first_Value(actuals)  over (partition by project_id order by decode(document,'D1',2,'D2',1,3)) d1_2   
 5  from   test_project;

ROJECT_ID D1       D1_2
--------- -- ----------
00001     D2         30
00002     D1         25
00003     D1         40
00004     D2         20

Report message to a moderator

Re: Selecting a row based on the existence of another [message #219195 is a reply to message #219141]

Tue, 13 February 2007 09:43

matpj
Messages: 115
Registered: March 2006
Location: London, UK

Senior Member

Perfect.
Thankyou so much!

Report message to a moderator

Re: Selecting a row based on the existence of another [message #219252 is a reply to message #219141]

Tue, 13 February 2007 12:34

Bill B
Messages: 1971
Registered: December 2004

Senior Member

USING THE ABOVE SAMPLE CODE AND A SELF JOIN.

create table test_project (Project_ID varchar2(10), Document varchar2(2), Actuals number);

Table created.

>insert into test_project values('P00001', 'D1', 50);

1 row created.

>insert into test_project values('P00001', 'D2', 30);

1 row created.

>insert into test_project values('P00002', 'D1', 25);

1 row created.

>insert into test_project values('P00003', 'D1', 40);

1 row created.

>insert into test_project values('P00004', 'D1', 10);

1 row created.

insert into test_project values('P00004', 'D2', 20);

1 row created.

1  SELECT A.PROJECT_ID,NVL(B.DOCUMENT,A.DOCUMENT) DOCUMENT,NVL(B.ACTUALS,A.ACTUALS) ACTUALS
  2  FROM TEST_PROJECT A, TEST_PROJECT B
  3  WHERE A.PROJECT_ID = B.PROJECT_ID(+)
  4  AND A.DOCUMENT = 'D1'
  5  AND B.DOCUMENT(+) = 'D2'
  6* ORDER BY A.PROJECT_ID;

PROJECT_ID DO    ACTUALS
---------- -- ----------
P00001     D2         30
P00002     D1         25
P00003     D1         40
P00004     D2         20

[Updated on: Tue, 13 February 2007 12:34]

Report message to a moderator

Re: Selecting a row based on the existence of another [message #219362 is a reply to message #219252]

Wed, 14 February 2007 02:50

pavuluri
Messages: 247
Registered: January 2007

Senior Member

wow
good logic
Thanks,
Srinivas

Report message to a moderator

Re: Selecting a row based on the existence of another [message #219459 is a reply to message #219362]

Wed, 14 February 2007 09:02

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Just another option for ya:

SQL> select * from test_proj;

PROJECT_ID DO    ACTUALS
---------- -- ----------
P00001     D1         50
P00001     D2         30
P00001     S1         30
P00002     D1         25
P00002     S1         25
P00003     D1         40
P00004     D1         10
P00004     D2         20

8 rows selected.

SQL> select project_id, document, actuals
  2     from (
  3             select project_id, document, actuals
  4             , row_number() over (partition by project_id order by document) rn
  5             from test_proj)
  6  where rn = 1
  7  or document = 'S1';

PROJECT_ID DO    ACTUALS
---------- -- ----------
P00001     D1         50
P00001     S1         30
P00002     D1         25
P00002     S1         25
P00003     D1         40
P00004     D1         10

6 rows selected.

Report message to a moderator

Previous Topic:	Oracle Multi Org Setup
Next Topic:	execution of procedure in sql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 04:19:48 CST 2024