how to supress duplicate values in the feild 1

[Iloveoracle]

Hi ,

I am giving one example to explain my problem.For example I have one table called social_behavour,wich shows the people list according to the organization and location.


ID ORGANIZATION LOCATION NAME DISPLAY ORDER
--- ----------- --------- ------ ----------
111 Childrens Foundation CAbranch W.Build 1
111 Childrens Foundation LAbranch A.Peter 2
111 Childrens Foundation NJbranch R.Sam 3
111 Childrens Foundation MWSAbranch P.Nancy 4
111 Childrens Foundation MWSAbranch L.Jack 5
111 Childrens Foundation MWSAbranch C.Carlin 6
111 Arben Areas NSAbranch Samul 7
111 Arben Areas NSAbranch Moghil 8
111 ArbenAreas MWbranch Jhon 9
111 Backward Steps NJbranch Samantha 10
111 BAckward Steps VTbranch J.William 11


This is the actual data in the database.Now In the report I have to display like this:

ID ORGANIZATION LOCATION NAME DISPLAYORDER
---- ----------- ---------- ----- ---------
111 ChildrensFoundation CAbranch W.Build 1
111 LAbranch A.Peter 2
111 NJbranch R.Sam 3
111 MWSAbranch P.Nancy 4
111 L.Jack 5
111 C.Carlin 6
111 Arben Areas NSAbranch Samul 7
111 Moghil 8
111 MWbranch Jhon 9
111 Backward Steps NJbranch Samantha 10
111 VTbranch J.William 11

I think you may understand this now.So my question is how can I supress duplicate values ,to show this in the report.

Thanks
gp

 

[TISIBS]

Hi,

this is a typical "Group Left" report.

Use the report wizard. In the second window choose "Group Left".
In the window for groups add ORGANIZATION to group level 1 and LOCATION to group level 2.

Timo

 

[Iloveoracle]

Hi Timo,
I think Group left won't work here.Problem is I have to display data order by display_order.If i make this group by it will display order by organization not by display_order.I think I didn't explaion properly,right now data in the databse is not in the order ,actual data in the database looks like this:




ID ORGANIZATION LOCATION NAME DISPLAY ORDER
--- ----------- --------- ------ ----------
111 Childrens Foundation MWSAbranch P.Nancy 4
111 Backward Steps NJbranch Samantha 10
111 BAckward Steps VTbranch J.William 11
111 Arben Areas NSAbranch Samul 7
111 Arben Areas NSAbranch Moghil 8
111 ArbenAreas MWbranch Jhon 9
111 Childrens Foundation NJbranch R.Sam 3
111 Childrens Foundation CAbranch W.Build 1
111 Childrens Foundation LAbranch A.Peter 2
111 Childrens Foundation MWSAbranch L.Jack 5
111 Childrens Foundation MWSAbranch C.Carlin 6

Now we have to display this data order by display_order in the report without duplicates in the organizationand location.


ID ORGANIZATION LOCATION NAME DISPLAYORDER
---- ----------- ---------- ----- ---------
111 ChildrensFoundation CAbranch W.Build 1
111 LAbranch A.Peter 2
111 NJbranch R.Sam 3
111 MWSAbranch P.Nancy 4
111 L.Jack 5
111 C.Carlin 6
111 Arben Areas NSAbranch Samul 7
111 Moghil 8
111 MWbranch Jhon 9
111 Backward Steps NJbranch Samantha 10
111 VTbranch J.William 11

 

[TISIBS]

Hi Iloveoracle,

change your SQL like this:

SELECT ORGANIZATION
,LOCATION
,NAME
,DISPLAY_ORDER
,MIN(display_order) over (PARTITION BY ORGANIZATION) ord1
,MIN(display_order) over (PARTITION BY ORGANIZATION,LOCATION) ord2
FROM yourtable

In the report as suggested above place ord1 in top position of group1 (organization) and
ord2 in top of group2 (location).

Timo

 

[Iloveoracle]

Hi Timo ,


Thanks for your quick response.Its working now.Really you saved my day.


Thanks
GP