Thread: A Query Doubt

Dear Krishna,

Kindly try this query and reply. I have tuned it at my end.
I hope it will not take much execution time.

Have a pleasant time

Hello,
The desired output can be achieved by this query

select * from (Table_name) where
( nvl(plotnumber,'NA') ,nvl(gangcode,'NA'), nvl(commonpool,0) )
in
(select nvl(plotnumber,'NA') ,nvl(gangcode,'NA'), nvl(commonpool,0)
from (Table_name)
where plotnumber = '&plotnumber'
group by nvl(plotnumber,'NA') ,nvl(gangcode,'NA'), nvl(commonpool,0)
)
/

If you want to tune it then a composite index on three of this columns will do.

Dear avtar259

Thank you for your response. Your query will display all the records from the table. Because your inner query will eliminate duplicate records. But you are selecting all the records once again in outer query based on the result returned by inner query. It will display all the records.My requirement is same plotnumber having more than one gangcode. First query given by srikumar_nair_it will surve that purpose.

Hi Krishna,

This query fulfills the requirement. Any performance difference from the ones given by others???


Query:
------

SELECT *
FROM TableName
WHERE plotnumber=&plot


* and & are best practices


Output:
-------

PLOTNUMBER LABOURCHARGES GANGCODE COMMONPOOL

P010330568 157.00 T0115 0.00
P010330568 162.00 T0132 0.00
P010330568 162.00 T0132 10.00

Ranganayaki.

Please test the below query for your result


select * from out_tab where
plot in (
select t.plot from
(
select min(rowid) rn, plot, gangcode
from out_tab
group by plot, gangcode) t
group by t.plot
having count(t.plot) > 1)
group by plot, labour, gangcode, common_pool;

See the result

PLOT LABOUR GANGCO COMMON_POOL
------------ ---------- ------ -----------
P010330568 157 T0115 0
P010330568 162 T0132 0
P010330568 162 T0132 10


Thanks