How to write a query to display records, same plotnumber having more than one gangcode (with different Gangcodes) and commonpool combination . Here I am giving sample data.
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.
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.
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;