Home » SQL & PL/SQL » SQL & PL/SQL » Selecting a row based on the existence of another
Selecting a row based on the existence of another [message #219141] Tue, 13 February 2007 02:49 Go to next message
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

Re: Selecting a row based on the existence of another [message #219172 is a reply to message #219141] Tue, 13 February 2007 08:43 Go to previous messageGo to next message
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;
Re: Selecting a row based on the existence of another [message #219175 is a reply to message #219141] Tue, 13 February 2007 08:58 Go to previous messageGo to next message
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
Re: Selecting a row based on the existence of another [message #219178 is a reply to message #219175] Tue, 13 February 2007 09:04 Go to previous messageGo to next message
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;
Re: Selecting a row based on the existence of another [message #219181 is a reply to message #219172] Tue, 13 February 2007 09:12 Go to previous messageGo to next message
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
icon14.gif  Re: Selecting a row based on the existence of another [message #219195 is a reply to message #219141] Tue, 13 February 2007 09:43 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Perfect.
Thankyou so much!

Re: Selecting a row based on the existence of another [message #219252 is a reply to message #219141] Tue, 13 February 2007 12:34 Go to previous messageGo to next message
Bill B
Messages: 1484
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 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
wow
good logic
Thanks,
Srinivas
Re: Selecting a row based on the existence of another [message #219459 is a reply to message #219362] Wed, 14 February 2007 09:02 Go to previous message
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.
Previous Topic: Oracle Multi Org Setup
Next Topic: execution of procedure in sql
Goto Forum:
  


Current Time: Sat Dec 10 09:22:00 CST 2016

Total time taken to generate the page: 0.17787 seconds