Home » SQL & PL/SQL » SQL & PL/SQL » unique records (oracle 9i linux)
unique records [message #362048] Sun, 30 November 2008 04:40 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
create table table_a1 (pk_id	number(5) ,
name varchar(15));
create table table_a2 (pk_id	number(5) ,
fk_id number(5) ,	
country	varchar(15),
state	varchar(15),
region varchar(15));


insert into table_a1	values	(1000,	'asia'	);
insert into table_a1	values	(1001,	'europe'	);
insert into table_a1	values	(1002,	'america'	);
insert into table_a1	values	(1003,	'africa'	);
commit;
insert into table_a2	values(	2000,1000,	'india',	'karnataka',	'bangalore'	);
insert into table_a2	values(	2001,1000,	'india',	'karnataka',	'guldarga'	);
insert into table_a2	values(	2002,1000,	'india',	'karnataka',	'beglumn'	);
insert into table_a2	values(	2003,1000,	'india',	'karnataka',	'mysore'	);
insert into table_a2	values(	2004,1000,	'india',	'andhra',	'hydarbad'	);
insert into table_a2	values(	2005,1000,	'india',	'andhra',	'chitor'	);
insert into table_a2	values(	2006,1000,	'pakistan'	,'lahore',	'abc'	);
insert into table_a2	values(	2007,1002,	'pakistan'	,'lahore',	'def'	);
insert into table_a2	values(	2008,1001,	'india',	'andhra',	'kronal'	);
insert into table_a2	values(	2009,1000,	'pakistan',	'lahore',	'ghj'	);
insert into table_a2	values(	2010,1000,	'pakistan',	'punjab',	'jkl'	);
commit;

how can i get the result like this without any procedure
fk_id	name	country	   state	region
1000	asia	india	   karnataka	bangalore
		pakistan   andhra	guldarga
			   lahore	beglumn
			   punjab	mysore
		        	        hydarbad
			        	chitor
				        abc
				        ghj
				        jkl

thanks a lot

[Updated on: Mon, 01 December 2008 08:40] by Moderator

Report message to a moderator

Re: unique records [message #362049 is a reply to message #362048] Sun, 30 November 2008 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What result do you want, in WORDS?
What did you already try?
What is your Oracle version with 4 DECIMALS.

Regards
Michel
Re: unique records [message #362056 is a reply to message #362049] Sun, 30 November 2008 05:41 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
What result do you want, in WORDS?
these are column "fk_id name country state region"

What is your Oracle version with 4 DECIMALS.
oracle version 9.2.0.1.0

What did you already try?
i tried with union union all ......
Re: unique records [message #362057 is a reply to message #362056] Sun, 30 November 2008 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
these are column "fk_id name country state region"

select fk_id country state region from table;

Regards
Michel

[Updated on: Sun, 30 November 2008 11:53]

Report message to a moderator

Re: unique records [message #362074 is a reply to message #362057] Sun, 30 November 2008 10:47 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
select  distinct t2.fk_id,t1.name,t2.country,t2.state,t2.region from table_a1 t1,table_a2 t2
where t2.fk_id=1000
and t2.fk_id=t1.pk_id;
Re: unique records [message #362076 is a reply to message #362048] Sun, 30 November 2008 11:07 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hope you got the result .

Smile
Rajuvan.
Re: unique records [message #362081 is a reply to message #362076] Sun, 30 November 2008 12:04 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
No....
can any suggest some ....idea...
Re: unique records [message #362084 is a reply to message #362048] Sun, 30 November 2008 12:40 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Create seperate views for each field (name,country,state,region) with rank analytic function and Join then only rank field.

Smile
Rajuvan.
Re: unique records [message #362085 is a reply to message #362081] Sun, 30 November 2008 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
oracle_coorgi wrote on Sun, 30 November 2008 19:04
No....
can any suggest some ....idea...

You didn't say what you want.

Regards
Michel

Re: unique records [message #362104 is a reply to message #362085] Sun, 30 November 2008 23:28 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
i had mentioned in my first post itself the reqiurment
how can i get the result like this without any procedure
Re: unique records [message #362115 is a reply to message #362104] Mon, 01 December 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain what you want WITH WORDS.

Regards
Michel
Re: unique records [message #362121 is a reply to message #362115] Mon, 01 December 2008 01:43 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
i have two table table_a1 and table_a2
for each fk_id i need to display distinct records.
i need to display the distinct records for each column in the select statement
eg for column fk_id i need to get one record 1000 and name one record asia and country two record for column state 4 records and region 9 records

sample output

fk_id,name,country,state,region theses are column

fk_id	name	country	    state	    region
1000	asia	india	    karnataka	   bangalore
		pakistan    andhra	   guldarga
			    lahore	   beglumn
			    punjab	   mysore
				           hydarbad
				           chitor
				           abc
				           ghj
				           jkl
Re: unique records [message #362237 is a reply to message #362121] Mon, 01 December 2008 07:06 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
can any pls look into it
Re: unique records [message #362238 is a reply to message #362237] Mon, 01 December 2008 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you please don't use IM speak.

Regards
Michel

[Updated on: Mon, 01 December 2008 08:35]

Report message to a moderator

Re: unique records [message #362251 is a reply to message #362238] Mon, 01 December 2008 08:31 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
Did You try the way @rajavu1 suggested ?
Or find here some sample code that gives You two columns
with a0 as (select rownum rn from dual connect by level<=10), 
     a1 as (select row_number() OVER (ORDER BY country) rn, country 
            from test_table_a2 group by country order by country),
     a2 as (select row_number() OVER (ORDER BY state)   rn, state   
            from test_table_a2 group by   state order by   state)
     (select a1.country, a2.state 
      from a0, a1, a2 where a0.rn=a1.rn(+) and a0.rn=a2.rn)
        order by a0.rn

COUNTRY	  STATE
india	  andhra
pakistan  karnataka
	  lahore
	  punjab

[Updated on: Mon, 01 December 2008 08:40] by Moderator

Report message to a moderator

Re: unique records [message #362252 is a reply to message #362251] Mon, 01 December 2008 08:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@_jum

Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


OP has an history of reluctance to work and just wait for us to give the solution. Don't encourage him to stay in his behaviour.

Regards
Michel
Re: unique records [message #362384 is a reply to message #362252] Tue, 02 December 2008 02:09 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
Michel and others as Michel quoted (homework and newbies OP has an history of reluctance to work and just wait for us to give the solution. Don''t encourage him to stay in his behaviour) i had gone through the net and some doc .pdf for as i was unable to get any ..... otherwise i would have posted the code which i had worked
this is a real work issue
SELECT   ROW_NUMBER () OVER (ORDER BY rownum) row_num,
               fk_id ,name, country ,state, region from (
with a0 as (select rownum rn from dual connect by level<=150),
a1 as (select row_number() OVER (ORDER BY fk_id) rn, fk_id 
            from table_a2 group by fk_id order by fk_id),
a2 as (select row_number() OVER (ORDER BY name) rn, name 
            from table_a1 group by name order by name),
			a3 as (select row_number() OVER (ORDER BY country) rn, country 
            from table_a2 group by country order by country),
			a4 as (select row_number() OVER (ORDER BY state) rn, state 
            from table_a2 group by state order by state),
			a5 as (select row_number() OVER (ORDER BY region) rn, region 
            from table_a2 group by region order by region)
			(select a1.fk_id , a2.name, a3.country, a4.state,a5.region 
      from a0, a1, a2,a3,a4,a5 
where a0.rn=a1.rn(+) and a0.rn=a2.rn(+) and a0.rn=a3.rn (+) 
and a0.rn=a4.rn(+) and a0.rn=a5.rn(+) and a1.fk_id=1000
	  ))        order by row_num

i have doubt in this as iam setting the connect by level<=150 i will get 150 record output as this can change as per the data cannot we have different way
how can i pass a where caluse in it when passed i get only one record as there is six record for it

[Updated on: Tue, 02 December 2008 02:33] by Moderator

Report message to a moderator

Re: unique records [message #364321 is a reply to message #362384] Mon, 08 December 2008 00:32 Go to previous message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
sorry for replying late ,this issue was fixed by application code....
as this was urgent ...
thanxs for the response

Thanxs and Regards
Previous Topic: Need help on inserting price values
Next Topic: order and group by
Goto Forum:
  


Current Time: Thu Dec 08 06:15:19 CST 2016

Total time taken to generate the page: 0.12133 seconds