Home » SQL & PL/SQL » SQL & PL/SQL » Ignore Duplicate Rows (SQL,9.2.0.8.0,XP)
Ignore Duplicate Rows [message #400735] Wed, 29 April 2009 07:01 Go to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Dear All

I want to count "chassis no" on a monthly basis and productwise means if i use date range from '01Jan08' to '31Dec08' then system automatically count chassis no against the product and put it into month as per sale.i mean to say if we sale 20 chassis no in January then it will apper in Jan montn and if we sale 30 chassis no in march then system it will display in March month but if feb month has no sale then i want to display Feb month also and i in order to achive my objective i am using the multiple query ,first of all i want to display my first query.

select distinct to_char(a.ro_clo_dt,'MMRRRR'),count(chasis_no)
from ro.ro_mst a,sv_pir b
where a.ro_clo_dt=b.dt_entry(+)
and b.dt_entry(+) between '01JAN08' AND '31DEC08'
and a.ro_clo_dt between '01JAN08' AND '31DEC08'
AND B.PROD_CD(+)='12'
group by to_char(a.ro_clo_dt,'MMRRRR')

This above query display the all month sale like this 


012008                0
022008                0
032008               21
042008                0
052008                0
062008                6
072008               29
082008                1
092008                0
102008               33
112008                0
122008               12


but when i try to add product like this
TO_CHA COUNT(CHASIS_NO) PRO
------ ---------------- ---
012008                0
022008                0
032008                0
032008               21 12
042008                0
052008                0
062008                0
062008                6 12
072008                0
072008               29 12
082008                0
082008                1 12
092008                0
102008                0
102008               33 12
112008                0
122008                0
122008               12 12


The system will display the following data.
TO_CHA COUNT(CHASIS_NO) PRO
------ ---------------- ---
012008                0
022008                0
032008                0
032008               21 12
042008                0
052008                0
062008                0
062008                6 12
072008                0
072008               29 12
082008                0
082008                1 12
092008                0
102008                0
102008               33 12
112008                0
122008                0
122008               12 12


if you observe the above display then you can find that dates are repeats for example
032008                0
032008               21 12
062008                0
062008                6 12
102008                0
102008               33 12
122008                0
122008               12 12

now i want to write such type of sql query through which all duplicate rows that contain 0 values will discard and if value is 0 and duplication not exist then system will not discard it,how can i solve this problem,kindly guide.

[Updated on: Wed, 29 April 2009 07:54] by Moderator

Report message to a moderator

Re: Ignore Duplicate Rows [message #400753 is a reply to message #400735] Wed, 29 April 2009 07:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER function and only keep rows with rn=1.

Regards
Michel
Re: Ignore Duplicate Rows [message #400784 is a reply to message #400735] Wed, 29 April 2009 09:27 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Thanks for the answer,I will try to use it but i am afraid to use it because i dont have a well knowledge of Analytical function.
Re: Ignore Duplicate Rows [message #400789 is a reply to message #400735] Wed, 29 April 2009 10:06 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
> i am afraid to use it because i dont have a well knowledge of Analytical function.
Realize SELECT can not change anything.It is READ ONLY operation.
Re: Ignore Duplicate Rows [message #401328 is a reply to message #400735] Mon, 04 May 2009 02:01 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
thanks for the suggestion of using function,its works but i am stuck with the condition you mentioned that is rn=1

I am using the following query.
Select distinct to_char(a.ro_clo_dt,'MMRRRR'),count(chasis_no),b.prod_cd,row_number() over
(partition by to_char(a.ro_clo_dt,'MMRRRR') order by b.prod_cd) aa
from ro.ro_mst a,sv_pir b
where a.ro_clo_dt=b.dt_entry(+)
and b.dt_entry(+) between '01JAN08' AND '31DEC08'
and a.ro_clo_dt between '01JAN08' AND '31DEC08'
AND B.PROD_CD(+)='12'
group by to_char(a.ro_clo_dt,'MMRRRR'),b.prod_cd

the query gives the following result.
TO_CHA COUNT(CHASIS_NO) PRO        AA
------ ---------------- --- ---------
012008                0             1
022008                0             1
032008               21 12          1
032008                0             2
042008                0             1
052008                0             1
062008                6 12          1
062008                0             2
072008               29 12          1
072008                0             2
082008                1 12          1
082008                0             2
092008                0             1
102008               33 12          1
102008                0             2
112008                0             1
122008               12 12          1
122008                0             2



now if you observe the above result then under the head/column of AA (row_number),you can find the values 2 how can i discard all records where values are 2.

I am trying to use it in a where clause row_number=2 but its not working,kindly suggest.
Re: Ignore Duplicate Rows [message #401336 is a reply to message #401328] Mon, 04 May 2009 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select * from (your query) where aa=1

Please format your query: use SQL Formatter for instance.

Regards
Michel
Re: Ignore Duplicate Rows [message #401343 is a reply to message #400735] Mon, 04 May 2009 02:36 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Please find below the formated query.

SELECT   DISTINCT To_char(a.ro_clo_dt,'MMRRRR'), 
                  Count(chasis_no), 
                  b.prod_cd, 
                  Row_number() 
                    OVER(PARTITION BY To_char(a.ro_clo_dt,'MMRRRR') ORDER BY b.prod_cd) aa 
FROM     ro.ro_mst a, 
         sv_pir b 
WHERE    a.ro_clo_dt = b.dt_entry (+) 
         AND b.dt_entry (+)  BETWEEN '01JAN08' AND '31DEC08' 
         AND a.ro_clo_dt BETWEEN '01JAN08' AND '31DEC08' 
         AND b.prod_cd (+)  = '12' 
GROUP BY To_char(a.ro_clo_dt,'MMRRRR'), 
         b.prod_cd 
Re: Ignore Duplicate Rows [message #401344 is a reply to message #401343] Mon, 04 May 2009 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Answer given in previous post.
In addition you don't need DISTINCT.

Regards
Michel

[Updated on: Mon, 04 May 2009 02:39]

Report message to a moderator

Re: Ignore Duplicate Rows [message #401345 is a reply to message #401344] Mon, 04 May 2009 02:45 Go to previous message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Thank you very much for your support and solve my problem,Now i know the importance of Analytical function.

Previous Topic: Convert Relational table into Hierarchical table (split from hijacked thread)
Next Topic: SQL Expression
Goto Forum:
  


Current Time: Tue Dec 06 14:14:01 CST 2016

Total time taken to generate the page: 0.20569 seconds