Selecting The Most Common Value from Relation - SQL Statement

Selecting the most common value from relation - SQL statement

select top 1 software 
from your_table 
group by software
order by count(*) desc 

Find most frequent value in SQL column

SELECT
  <column_name>,
  COUNT(<column_name>) AS `value_occurrence` 

FROM
  <my_table>

GROUP BY 
  <column_name>

ORDER BY 
  `value_occurrence` DESC

LIMIT 1;

Replace <column_name> and <my_table>. Increase 1 if you want to see the N most common values of the column.

how can I select most common values in MySQL?

To get the most frequent value in a particular column, do:

SELECT examplecolumn1, COUNT(*) AS count
FROM Table1
GROUP BY examplecolumn1
ORDER BY count DESC
LIMIT 1

If you want this for multiple columns in a single query, just combine them with UNION:

SELECT examplecolumn1, COUNT(*) AS count
FROM Table1
GROUP BY examplecolumn1
ORDER BY count DESC
LIMIT 1
UNION
SELECT examplecolumn2, COUNT(*) AS count
FROM Table1
GROUP BY examplecolumn2
ORDER BY count DESC
LIMIT 1

Selecting rows with the most repeated values at specific column

If I understand this correctly you want to rank the countries per competition count and show the highest ranking country (or countries) with their count. I suggest you use RANK for the ranking.

select country, competition_count
from
(
  select 
    s.country,
    count(*) as competition_count,
    rank() over (order by count(*) desc) as rn
  from sportsman s
  inner join result r using (sportsman_id)
  group by s.country
) ranked_by_count
where rn = 1
order by country;

If the order of the result rows doesn't matter, you can shorten this to:

select s.country, count(*) as competition_count      
from sportsman s
inner join result r using (sportsman_id)
group by s.country
order by count(*) desc
fetch first rows with ties;

How to get the most frequent value SQL

The "most frequently occurring value" in a distribution is a distinct concept in statistics, with a technical name. It's called the MODE of the distribution. And Oracle has the STATS_MODE() function for it. https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions154.htm

For example, using the EMP table in the standard SCOTT schema, select stats_mode(deptno) from scott.emp will return 30 - the number of the department with the most employees. (30 is the department "name" or number, it is NOT the number of employees in that department!)

In your case:

select stats_mode(h.name) from (the rest of your query)

Note: if two or more hotels are tied for "most frequent", then STATS_MODE() will return one of them (non-deterministic). If you need all the tied values, you will need a different solution - a good example is in the documentation (linked above). This is a documented flaw in Oracle's understanding and implementation of the statistical concept.

Bigquery find most common value when other column is same

Below is for BigQuery Standard SQL

#standardSQL
SELECT * EXCEPT(ids), 
  (SELECT id FROM UNNEST(ids) id GROUP BY id ORDER BY COUNT(1) DESC LIMIT 1) New_Family_id
FROM (
  SELECT *, ARRAY_AGG(FamilyId) OVER(PARTITION BY ProductTitleNL) ids
  FROM `project.dataset.table`
)

You can tesp, play with above using dummy data from your question as below

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 9200000005045711 GlobalId, '! at Gun Point...' ProductTitleNL, 9200000005045710 FamilyId UNION ALL
  SELECT 9200000003809684, '! at Gun Point...', 9200000011427871 UNION ALL
  SELECT 9200000011427872, '! at Gun Point...', 9200000011427871 UNION ALL
  SELECT 1001004011099420, 'Russian Dat', 34388968 UNION ALL
  SELECT 1001004011099421, 'Russian Dat', 35434738 UNION ALL
  SELECT 9200000000530359, '!!Nos Vemos!', 9200000000530358 UNION ALL
  SELECT 9200000000530343, '!!Nos Vemos!', 9200000000530342 
)
SELECT * EXCEPT(ids), 
  (SELECT id FROM UNNEST(ids) id GROUP BY id ORDER BY COUNT(1) DESC LIMIT 1) New_Family_id
FROM (
  SELECT *, ARRAY_AGG(FamilyId) OVER(PARTITION BY ProductTitleNL) ids
  FROM `project.dataset.table`
)   

with result

Row GlobalId            ProductTitleNL      FamilyId            New_Family_id    
1   9200000005045711    ! at Gun Point...   9200000005045710    9200000011427871     
2   9200000003809684    ! at Gun Point...   9200000011427871    9200000011427871     
3   9200000011427872    ! at Gun Point...   9200000011427871    9200000011427871     
4   9200000000530359    !!Nos Vemos!        9200000000530358    9200000000530358     
5   9200000000530343    !!Nos Vemos!        9200000000530342    9200000000530358     
6   1001004011099420    Russian Dat 34388968                    34388968     
7   1001004011099421    Russian Dat 35434738                    34388968     

---

Related Topics