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 ?

Answer 1

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