Home » SQL & PL/SQL » SQL & PL/SQL » Filtering rows
Filtering rows [message #207904] Thu, 07 December 2006 05:05 Go to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Hi All,
I am in a peculiar situation, don't know how to write query for this. I am illustrating this with an example:

CREATE TABLE T1(EMPLOYEE_ID NUMBER, PROJECT_ID NUMBER, TASK_ID NUMBER, RATE NUMBER)

INSERT INTO T1 VALUES(100, 354, NULL, 10)
INSERT INTO T1 VALUES(100, NULL, 354.1, 12)
INSERT INTO T1 VALUES(200, 202, NULL, 15)
INSERT INTO T1 VALUES(210, NULL, 203.1, 14)
INSERT INTO T1 VALUES(300, 350, NULL, 18)
INSERT INTO T1 VALUES(300, NULL, 350.1, 19)


I would need to fetch the rows on the below criteria:

If an employee_id has 2 rows one with Project_ID not null and another with Task_ID not null, the row with Task_ID not null should show up and not the row with the Project_ID.

However if the employee_id has only 1 row then it can show up irrespective of PROJECT_ID/TASK_ID being null.

So the output should be:

EMPLOYEE_ID	PROJECT_ID   TASK_ID	RATE
100			      354.1	12
210			      203.1	14
200		202			15
300			      350.1	19


The output contains only 1 row for employee_id 100 and 300 (TASK_ID not null took the preference)

Can someone help how to write a query to show this?

Regards,
Anand
Re: Filtering rows [message #207905 is a reply to message #207904] Thu, 07 December 2006 05:11 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Untested SQL, but it might give you some ideas
SELECT *
FROM t1
WHERE ROWID NOT IN (
    SELECT ROWID
    FROM t1 a
    WHERE project_id IS NOT NULL
    AND EXISTS (
        SELECT 1
        FROM   t1 b
        WHERE  b.employee_id = a.employee_id
        AND    b.task_id IS NOT NULL
        AND    b.rowid <> a.rowid
    ) 
)


Ross Leishman
Re: Filtering rows [message #207928 is a reply to message #207905] Thu, 07 December 2006 07:10 Go to previous messageGo to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Thanks Ross,
Your query does work. I a set of sample data but in actual this table contains more fields and around 500000 rows.
This query will be a resource intensive query.
Do we have an alternative?

Regards,
Anand
Re: Filtering rows [message #207952 is a reply to message #207928] Thu, 07 December 2006 08:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Analytics anybody?
select employee_id
      ,project_id
      ,task_id
      ,rate
from (select t1.*
            ,count(*) over (partition by employee_id) rows_per_emp
            ,case when task_id is not null then 'T' when project_id is not null then 'P' end t_or_p
      from   t1)
where rows_per_emp = 1
or   (rows_per_emp > 1 and t_or_p = 'T');

EMPLOYEE_ID PROJECT_ID    TASK_ID       RATE
----------- ---------- ---------- ----------
        100                 354.1         12
        200        202                    15
        210                 203.1         14
        300                 350.1         19

Re: Filtering rows [message #208163 is a reply to message #207905] Fri, 08 December 2006 07:03 Go to previous message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Hi Ross,
Your query also works fine if I remove b.task_id is not null.

SELECT *
FROM t1
WHERE ROWID NOT IN (
    SELECT ROWID
    FROM t1 a
    WHERE project_id IS NOT NULL
    AND EXISTS (
        SELECT 1
        FROM   t1 b
        WHERE  b.employee_id = a.employee_id
        AND    b.task_id IS NOT NULL  -- can be removed
        AND    b.rowid <> a.rowid
    ) 
)


I am slightly confused how this query works, can you please explain me the inner part of the query i.e.

SELECT ROWID
    FROM t1 a
    WHERE project_id IS NOT NULL
    AND EXISTS (
        SELECT 1
        FROM   t1 b
        WHERE  b.employee_id = a.employee_id
        AND    b.task_id IS NOT NULL  -- can be removed
        AND    b.rowid <> a.rowid  --I did not understand this part especially
    ) 




Thanks for your time.
Anand
Previous Topic: issue of environment variable
Next Topic: plz tell diff. b/w these quries
Goto Forum:
  


Current Time: Fri Dec 02 14:09:56 CST 2016

Total time taken to generate the page: 0.19200 seconds