spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories

SQL query to return most common, 2nd most common, 3rd most common occurrences


You can just use GROUP_CONCAT() aggregate function:

SELECT
  Fruit, GROUP_CONCAT(Freshness ORDER BY cnt DESC)
as Common
FROM (
  SELECT Fruit, Freshness, COUNT(*) cnt
  FROM
    fruits
  GROUP BY
    Fruit, Freshness
) s

that will return values like:

Fruit  | Common
---------------------
Banana | New,Old,Ripe
Cherry | New,Ripe
...    | ...

but if you want to divide the result in three columns, you can combine the previous query and use SUBSTRING_INDEX() to extract the first, sencond, and third value from the comma separated values:

SELECT
  Fruit,
  SUBSTRING_INDEX(Common, ',', 1) AS most,
  CASE WHEN CommonLIKE '%,%'
       THEN
SUBSTRING_INDEX(SUBSTRING_INDEX(Common, ',', 2),
',', -1) END AS second_most,
  CASE WHEN CommonLIKE '%,%,%'
       THEN
SUBSTRING_INDEX(SUBSTRING_INDEX(Common, ',', 3),
',', -1) END AS third_most
FROM (
  SELECT
    Fruit, GROUP_CONCAT(Freshness ORDER BY cnt
DESC) as Common
  FROM (
    SELECT Fruit, Freshness, COUNT(*) cnt
    FROM
      fruits
    GROUP BY
      Fruit, Freshness
  ) s
  GROUP BY
    Fruit
  ) s

Categories : Mysql

Related to : SQL query to return most common, 2nd most common, 3rd most common occurrences
what is the main difference between Common File Dialog and Common Item Dialog in c++?
Common File Dialog was just a custom DialogBog. On the other end, Common Item Dialog is a full COM server. Microsoft's documentation says : The Common Item Dialog implementation found in Windows Vista provides several advantages over the implementation provided in earlier versions: Supports direct use of the Shell namespace through IShellItem instead of using file system paths. Enables simple

Categories : C++
How can I share common js/css in an MVC app?
If you are going to have a lot of this, build your own nuget packages and host them on a share or on your own private nuget server. It's remarkably easy to do, you get to execute scripts if necessary when the package is added to your project, and nuget packages are versioned, so you when you update your library, you can release an updated package with an incremented version number, and they will

Categories : Asp Net
Unable to find the class 'Common'
The Common class is part of the sample project and can be found in the sample directory. SonicHomeMQ8.6samplesManagement untimeAPIjavaProxyCommon.java -- utility methods used by the above samples Otherwise the jars can be found in the lib directory. The class path for the Mgmt api is : MGMT_CLASSPATH=%SONICMQ_LIB%mgmt_client.jar;%SONICMQ_LIB%mgmt_config.jar;%SONICMQ_LIB%mfcontext.jar

Categories : Misc
Sesame caching common queries
I assume that what you want to "cache" is the query result for a given query with specific value. You can very easily build such a cache yourself. Just create a class for the general query that internally keeps a reference to a HashMap that maps from a value key (e.g. the placeid for your example query) to a query result: HashMap<URI, TupleQueryResult> cache = new HashMap<>(); Then

Categories : Caching
How can we select not common columns in SQL Server
Use a correlated not exists in the where clause to get columns from Table1 that is not exist in Table2. select A.COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS as A where A.TABLE_NAME = 'Table1' and not exists ( select * from INFORMATION_SCHEMA.COLUMNS as B where A.COLUMN_NAME = B.COLUMN_NAME and B.TABLE_NAME = 'Table2'

Categories : SQL
Recently Add
Rails: retrieve data from multiple tables without association
Laravel: Querying based on Input. If input is empty, get all
MYSQL Workbech connection to local network database?
Delete a record from table2 after fetched into table1 in sas
Remove garbage from database
Can't get load_file() working in mysql
Select average from join MYSQL
Simulating lag function on MySQL
Authentication mysql to .Net
Why does this MySQL XOR query return 0?
PHP update query with syntax error
Wordpress wpdb->query problems with importing data
Sql- how to use relational field in condition
Importing XML into phpMyAdmin using SQL
Group_Concat with left join is slowing down. I need all rows in left table as well
MySQL: Strange AUTO_INCREMENT
Mysql: performance partitions in joining tables
MySql query to get rows with only one connected row
SQL selecting concentration of holding in a portfolio
MySQL - Another way to do this to reduce the amount of Joins? - Or short hand syntax
MySql: Concatenate column where there are duplicates
#1064 - You have an error in your SQL syntax. Why?
upload updated details in excel to mysql database
MySql filter query
How to create tables with a prefix in a more maintainable way
Nagios : Setting alerts based in value returned
How to get row values of a datagridview and pass it to another form using a button in thesame row
Mysql: Selecting 4 entries in a table unique 2 and 2 dependent on row content
How do I declare foreign keys referencing multi-value primary key in mysql?
How to use GROUP_CONCAT with MAX(CASE WHEN...)?
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.