asked on

select max

I have following query
SELECT ppa.project_type,ppa.segment1 project_no, ppa.project_id, pt.task_id,pt.WBS_LEVEL,pt.READY_TO_DISTRIBUTE_FLAG,pt.RECORD_VERSION_NUMBER,
tASK_NUMBER,
pt.task_name,
pt.billable_flag,
pt.chargeable_flag,
pt.PARENT_TASK_ID
FROM apps.pa_tasks pt, apps.pa_projects_all ppa
WHERE ppa.project_id = pt.project_id
AND ppa.segment1 IN ('172018')
-- and pt.READY_TO_DISTRIBUTE_FLAG = 'N'
AND pt.WBS_LEVEL in (select max(to_number(b.wbs_level)) from pa_tasks b where pt.task_id=b.task_id)

I want to select the max of wbs_level but the query is not working

pardeshirahul

ASKER

if wbs_level is 1 2 and 3 then select 2 and 3

pardeshirahul

ASKER

if it is 1 only select 1

PortletPaul

The following snippet is the equivalent of wbs_level = wbs_level

FROM apps.pa_tasks pt
...
WHERE ...
AND pt.WBS_LEVEL IN (
      SELECT
            MAX(to_number(b.wbs_level))
      FROM pa_tasks b
      WHERE pt.task_id = b.task_id
)

Open in new window

(when pt.task_id = b.task_id there is a single wbs_level value in pt that is exactly the same single wbs_level value in b)

"I want to select the max of wbs_level"
This may be very difficult, or it might be easy, the problem is we know nothing about your data, so it is very hard to offer a suggestion. However the following might help. The logic is to calculate the maximum wbs_level in each project, then only return that level of the WBS (per project)

SELECT
      *
FROM (

      SELECT
            ppa.project_type
          , ppa.segment1        project_no
          , ppa.project_id
          , pt.task_id
          , pt.WBS_LEVEL
          , pt.READY_TO_DISTRIBUTE_FLAG
          , pt.RECORD_VERSION_NUMBER
          , tASK_NUMBER
          , pt.task_name
          , pt.billable_flag
          , pt.chargeable_flag
          , pt.PARENT_TASK_ID
          , MAX(to_number(pt.wbs_level)) OVER (PARTITION BY pt.project_id) maxwbs_lvl
      FROM apps.pa_tasks pt
      INNER JOIN apps.pa_projects_all ppa ON ppa.project_id = pt.project_id
      WHERE ppa.segment1 IN ('172018')
-- and pt.READY_TO_DISTRIBUTE_FLAG = 'N'
) d
WHERE wbs_level = maxwbs_lvl

Open in new window

If thi does not help can you provide some samples of the data in your task table (scrub any private stuff)

Anthony Parra

Try this:

SELECT ppa.project_type,ppa.segment1 project_no, ppa.project_id, pt.task_id,pt.WBS_LEVEL,pt.READY_TO_DISTRIBUTE_FLAG,pt.RECORD_VERSION_NUMBER,
tASK_NUMBER,
pt.task_name,
pt.billable_flag,
pt.chargeable_flag,
pt.PARENT_TASK_ID
FROM apps.pa_tasks pt, apps.pa_projects_all ppa
WHERE ppa.project_id = pt.project_id
AND ppa.segment1 IN ('172018')
-- and pt.READY_TO_DISTRIBUTE_FLAG = 'N'
AND pt.WBS_LEVEL in (select max(to_number(b.wbs_level)) from pa_tasks b)

slightwv (䄆 Netminder)

What you have asked can mean many different things. You might also be able to get what you want with RANK, DENSE_RANK or ROW_NUMBER.

As Paul as asked:
Please post some simple sample data and expected results.

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

SELECT
ppa.project_type,
ppa.segment1 project_no,
ppa.project_id,
pt.task_id,
pt.WBS_LEVEL,
pt.READY_TO_DISTRIBUTE_FLAG,
pt.RECORD_VERSION_NUMBER,
tASK_NUMBER,
pt.task_name,
pt.billable_flag,
pt.chargeable_flag,
pt.PARENT_TASK_ID
FROM
apps.pa_tasks pt
INNER JOIN apps.pa_projects_all ppa ON ppa.project_id = pt.project_id
INNER JOIN (SELECT wbs_level, task_id ,
row_number() over (order by task_id order by wbs_level DESC) rown
FROM pa_tasks) x
ON x.task_id = pt.task_id
WHERE ppa.segment1 = ('172018')
AND x.rown = 1;