CODEWITHSUNDEEP
sqloracle-database
Peter Valentine
Peter Valentine

Reputation: 85

SQL Query to select most popular data from table

I want to select the most popular name from this table which is Peter

How can I do this? I have tried different SQL query but none worked.

SQL> select * from OurStaffs;

   SID S_LOCATION S_SITE  S_RAN S_NAME
------ ---------- ------- ----- ----------
     1 hotel      root1   rank1 Peter
     2 barbeach   root3   rank0 John
     3 island     root2   rank2 peter
     4 hotel      root2   rank0 ujah

11 rows selected.

Upvotes: 3

Views: 854

Answers (3)

einpoklum
einpoklum

Reputation: 131525

If you're case-sensitive, then something like:

SELECT
   S_NAME,
   count() AS cnt 
FROM
   OurStaffs 
GROUP BY
   S_NAME 
ORDER BY
   cnt DESC
LIMIT 1;

If you want to be case-insensitive, replace S_NAME with something like tolower(S_NAME) or lower(S_NAME) etc, depending on the DBMS you're using. For Oracle, it should be:

SELECT
   lower(S_NAME),
   count() AS cnt 
FROM
   OurStaffs 
GROUP BY
   lower(S_NAME)
ORDER BY
   cnt DESC 
LIMIT 1;

(You'd get the same result with upper() of course.)

Upvotes: 2

Raj More
Raj More

Reputation: 48016

I assumed SQL Plus with Oracle.. Try this out - it also accounts for case sensitivity.

With OurStaffs AS
(
      Select 1 SID , 'hotel' S_LOCATION, 'root1' S_SITE, 'rank1' S_RAN, 'Peter' S_NAME From Dual
Union Select 2, 'barbeach', 'root3', 'rank0', 'John' From Dual
Union Select 3, 'island', 'root2', 'rank2', 'peter' From Dual
Union Select 4, 'hotel', 'root2', 'rank0', 'ujah' From Dual
)

Select *
From
(
  Select Upper (S_Name) S_Name, Count(*)
  From OurStaffs
  Group By Upper (S_Name)
  Order by 2 Desc
)x
Where RowNum = 1

Upvotes: 1

David542
David542

Reputation: 110113

You could group by the name and then order by the count. Here's an example:

SELECT
   S_NAME
FROM
    OurStaffs
GROUP BY
    S_NAME
ORDER BY
    COUNT(S_NAME) DESC
LIMIT 1

Upvotes: 0

Related Questions