Home » SQL & PL/SQL » SQL & PL/SQL » To select the rows, for which most of the columns have values (Oracle 9i, Unix)
To select the rows, for which most of the columns have values [message #350234] Wed, 24 September 2008 08:19 Go to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Hi All,

I need one help to select the only rows for duplicate name, for which most of the columns have values. Like below is the test data.

[B]Username	Email	Bes	server	mobname [/B]
PQR	pqr@gmail.com	yyy-09-hh	excha	hutch
ABC	abc@yahoo.com			
ABC	abc@yahoo.com	hhh-22-yy	excha	mobile
NYU	nyu@yahoo.com	nhy-88-yu	excha	three
ABC	abc@yahoo.com			mobile


The output should be

[B]Username	Email	Bes	server	mobname [/B]
PQR	pqr@gmail.com	yyy-09-hh	excha	hutch
ABC	abc@yahoo.com	hhh-22-yy	excha	mobile
NYU	nyu@yahoo.com	nhy-88-yu	excha	three


I've tried to compare length of all the columns of duplicate rows but it will not work in my scenario. I think I would also like to mention that using NULL and NOT NULL is not permissible.

Can anybody help me out in writing this query.

Regards
Amit
Re: To select the rows, for which most of the columns have values [message #350238 is a reply to message #350234] Wed, 24 September 2008 08:24 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
amit.pandey wrote on Wed, 24 September 2008 09:19

I need one help to select the only rows for duplicate name, for which most of the columns have values


Define "most."

Quote:
I think I would also like to mention that using NULL and NOT NULL is not permissible.



Why give this ridiculous requirement? NULL and NOT NULL are part of Oracle. Why do you insist on not using them?
Re: To select the rows, for which most of the columns have values [message #350245 is a reply to message #350234] Wed, 24 September 2008 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
. I think I would also like to mention that using NULL and NOT NULL is not permissible.

Why?
Use an indicator, for instance:
decode(col1,null,0,1)+decode(col2,null,0,1)+...

Regards
Michel
Re: To select the rows, for which most of the columns have values [message #350247 is a reply to message #350234] Wed, 24 September 2008 08:41 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
You could try this:

select Username,Email,Bes,server,mobname
from (
		select Username,Email,Bes,server,mobname,
			rank() over (
				order by nvl2(Username,1,0)+nvl2(Email,1,0)+nvl2(Bes,1,0)+nvl2(server,1,0)+nvl2(mobname,1,0) desc
			) as rnk
		from your_table
	)
where rnk = 1
/


Bye Alessandro

[Updated on: Wed, 24 September 2008 08:42]

Report message to a moderator

Re: To select the rows, for which most of the columns have values [message #350252 is a reply to message #350234] Wed, 24 September 2008 08:59 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
Very Very Very nice one Alessandro !!!
Good work !!
Re: To select the rows, for which most of the columns have values [message #350254 is a reply to message #350247] Wed, 24 September 2008 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Alessandro,

Once again:
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.


Regards
Michel
Re: To select the rows, for which most of the columns have values [message #350263 is a reply to message #350247] Wed, 24 September 2008 09:18 Go to previous messageGo to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Thanka a Ton Alessandro !! Smile

Really it is a nice solution ..

regards
Amit
Re: To select the rows, for which most of the columns have values [message #350274 is a reply to message #350254] Wed, 24 September 2008 10:05 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
I read it, but in my opinion it is a trick, not a newbie question. That query involves a condition based on an elaboration of multiple columns, the rank analytic function and the nvl2 function that may not be considered easy to combine together for a newbie.

The documentation doesn't cover a so specific arguments witch is not that easy to explain, omitting to say explicitly how to do it (nominating rank and nvl2 or equivalents), but if that is you meant I got your point.

Bye Alessandro
Re: To select the rows, for which most of the columns have values [message #350281 is a reply to message #350274] Wed, 24 September 2008 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
that may not be considered easy to combine together for a newbie.

You can point to the documentation for every function you'd use in your solution and clue (as I did) and let OP try with these and come back with his tries if he doesn't find something working.
I know this is harder, takes more time and gives far less reason to be proud and satisfied than posting a "definitive" query.
In addition we will rarely get thanks with this way and never exclamation mark.

Regards
Michel


[Updated on: Wed, 24 September 2008 10:29]

Report message to a moderator

Re: To select the rows, for which most of the columns have values [message #350290 is a reply to message #350263] Wed, 24 September 2008 10:38 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
you should try, what happens when two identic username have the same number of "filled" columns, like:
DEF	def@yahoo.com	        mobile
DEF                      excha  tree

Re: To select the rows, for which most of the columns have values [message #350428 is a reply to message #350234] Thu, 25 September 2008 00:58 Go to previous message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
Both will be retrieved !
Previous Topic: error while adding column on table
Next Topic: TYPICAL QUESTION
Goto Forum:
  


Current Time: Thu Dec 08 14:42:18 CST 2016

Total time taken to generate the page: 0.07127 seconds