I have a table with 1 column called 'Name' . The values in the table are as follows:
select * from table
Name
--------
Gaurav Goel
Nitin Jain
Gaurav Sharma
Gaurav Kapoor
Sanjeev Sinha
Sanjeev kapoor
Nishi Kant
Nitin Agrawal
Now I have to retrieve data in order of popularity. The popularity of any name is defined as follows:
Extract the first names for all entries. The one with more popular first names have to be shown first. In case of ties, the name which comes first in the table has to be displayed first. In the above sample values, the required output is :
Gaurav Goel
Gaurav Sharma
Gaurav Kapoor
Nitin Jain
Sanjeev Sinha
Sanjeev kapoor
Nitin Agrawal
Nishi Kant
The query written is :
select name from
(
select rownum rn,name,
substr(name,1,instr(name,' ')) first_name,
count(substr(name,1,instr(name,' '))) over(partition by substr(name,1,instr(name,' '))) occurence
from names
)
order by occurence desc,rn
It seems to work!! Can anybody plz give a better solution??
Posts
oem cpu jul 23
-
this blog is no longer maintained. go there : OEM patch |
LaurentSchneider.Com (wordpress.com)
7 months ago
3 comments:
When we talk of priority first, then why is it that this Qry is displaying Sanjeev below .. it should ideally first show the priority names and then the Alphabetical order.
Sulabh, I think u have missed something in the problem statement. The problem states that the names with higher popularity should be displayed first. However if the popularity of two names is equal, then the names should be displayed in the same order as they are present in table. In this case , Nitin and Sanjeev have same popularity (Both are present 2 times). So they will be dislayed in the same order as they are present in table. So "Nitin Jain" is displayed before "Sanjeev Sinha" and then "Sanjeev Kapoor". Next comes "Nitin Agrawal" since it has more popularity than "Nishi Kant". Actually this was the main problem faced during the formation of query.
I hope it makes sense.
This query also give the result as reuired in the problem
select ename from
(select ename,rownum,count(*) over (partition by substr(ename,1,instr(ename,' '))) count from name
group by ename,rownum
order by 3 desc,2)
Cheers
Kartik
Post a Comment