select only one name from multiple/many names

Question

I want to select only one name from table where the person(name) is the most times in the table..

table looks like (the import_rec ix_k) :

product_name     product_id     man_name
  product1           1        mame1|''|last_name1|''|ID
  product2           2        name1|''|last_name1|''|ID
  product3           3        name2|''|last_name2|''|ID
  product4           4        name1|''|last_name1|''|ID
  product5           5        name1|''|last_name1|''|ID
  product6           6        name3|''|last_name3|''|ID

EDIT: so i want to select only name1-last_name1 from the table

my query:

select u.name||' '||u.last_name from X_USERS u, items x, import_rec ix_k 
where u.id = x.id_man and x.id = ix_k.code_id and 
"actual_table".rid = ix_k.rid_o and rownum = 1

How can i select the name&last_name which is the most times in the table ?

Hi Georgy, you should to include a question in your post. It's nice you explain what you tried. Now, you should to explain what are you looking for and why your query is not enough to you. — dani herrera, Nov 30 '18 at 07:53
Hi Dani, I thought the question was clear.. Sorry. My query returns only preson (name) where rownum is 1.. so it can be name1 or 2 or 3.. not the name which is the most times in the table. Maybe i should group them or idk... — Georgy, Nov 30 '18 at 08:13

score 1 · Answer 1 · answered Nov 30 '18 at 08:26

You want

only one name from table where the person(name) is the most times in the table

If you are using 12c and above, you may use:

select * FROM t  
   ORDER BY COUNT(*) OVER ( PARTITION BY man_name ) 
DESC FETCH FIRST 1 ROWS ONLY;

For older versions, you may use ROWNUM with a subquery.

select * FROM
( select * FROM t  
     ORDER BY COUNT(*) OVER ( PARTITION BY MAN_NAME ) 
DESC ) WHERE rownum = 1;

desa · Accepted Answer · 2018-12-05T09:13:21.523

What you can do is using a Common Table Expression and ROW_NUMBER. You let the rownumber function count how many duplicates of [man_name] there is and order your table by the rownumber in descending order, so the highest count will be first in the table. Then you select the first row from your ordered table containing the row-number column. It will look something like this;

WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY man_name ORDER BY Product_id) AS rn
, product_name
, product_id
, man_name
FROM (yourtable/s)
)
SELECT TOP 1  
  rn 
, product_name
, product_id
, man_name
FROM CTE 
ORDER BY rn DESC

select only one name from multiple/many names

2 Answers2