How to Select record with the maximum value in a GROUP BY?

How to Select record with the maximum value in a GROUP BY?

Content Index :

How to Select record with the maximum value in a GROUP BY?
Tag : mysql , By : Nandor Devai
Date : November 27 2020, 01:01 AM

No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Maximum in group by active record query

Tag : ruby-on-rails , By : WellBeing
Date : March 29 2020, 07:55 AM
this one helps. I have a Model which has two attributes (I am showing only two as only those two columns are needed) , If you are specifying a place first you can do:
        group("place_id,user_id").order("count(user_id) DESC").first
        merge(MyModel.group("place_id,user_id").order("count(user_id) DESC"))

Combining Active Record group, join, maximum & minimum

Tag : sql , By : snapshooter
Date : March 29 2020, 07:55 AM
may help you . I'm trying to get to grips with the Active Record query interface. I have two models: , I think this will allow you to calculate maximum per genre:
Movie.joins(:datapoints).where(datapoints: {timestamp: (Time.now)..(Time.now+1.year)}).group(:genre).maximum(:cumulative_downloads)
rel = Movie.joins(:datapoints).where(datapoints: {timestamp: (Time.now)..(Time.now+1.year)}).group(:genre)
mins = rel.minimum(:cumulative_downloads)
maxs = rel.maximum(:cumulative_downloads)
res = {}
maxs.each{|k,v| res[k] = v-mins[k]}
# get the genre and diff per movie
result = Movie.select('movies.genre, MAX(datapoints.cumulative_downloads)-MIN(datapoints.cumulative_downloads) as diff').joins(:datapoints).group(:movie_id) 
# sum the diffs per genre
per_genre = Hash.new(0)
result.each{|m| per_genre[m.genre] += m.diff}
# get the genre and diff per movie
result = Movie
    .select('movies.movie_id, movies.genre, MAX(datapoints.cumulative_downloads)-MIN(datapoints.cumulative_downloads) as diff')
    .group('movies.movie_id, movies.genre') 
# sum the diffs per genre
per_genre = Hash.new(0)
result.each{|m| per_genre[m.genre] += m.diff}

select maximum n records from table with at most one record per group

Tag : mysql , By : lifchicker
Date : March 29 2020, 07:55 AM
will help you I have a table with a simple schema: user_id, score and I would like to extract the n records with the highest score, such that each user_id is only represented once.
SELECT user_id, MAX(score) FROM table GROUP BY user_id ORDER BY MAX(score) DESC LIMIT 3;

Group by Total per week, and only show record with the maximum sum (Access)

Tag : sql , By : jgood
Date : March 29 2020, 07:55 AM
I hope this helps you . Didn't know RDBMS so I assumed SQL SERVER and therefore that a CTE (Common Table Expression) would work.
We can avoid the cte by using subqueries if needed though... Also I eliminated spaces in column names as I didnt' know if I should use ` or [ or " to escape the field names....
With CTE AS (SELECT ProjectName, Origin, Week, count(projectname) as myCount
    GROUP BY ProjectName, Origin, week)
SELECT A.ProjectName, A.Origin, A.Week, A.MyCount
INNER JOIN (SELECT ProjectName, Origin, Max(myCount) MaxMyCount 
            FROM CTE
            GROUP BY ProjectName, Origin) B
   on A.Projectname = B.ProjectName
  and A.Origin = B.Origin
  and A.myCount = B.MaxMyCount
ORDER BY ProjectName, Origin, Week

Mysql Agregate function to select maximum and then select minimum price within that group

Tag : mysql , By : Sigtryggur
Date : March 29 2020, 07:55 AM
I wish did fix the issue. This question is asked and answered with tedious regularity in SO. If only the algorithm was better at spotting duplicates. Anyway...
  FROM my_table x 
     ( SELECT discount,MIN(price) min_price FROM my_table GROUP BY discount) y 
    ON y.discount = x.discount 
   AND y.min_price = x.price;
Related Posts Related QUESTIONS :
  • Can't connect local database. Sequel Pro encountered an unexpected error
  • How do I change the auto increment counter in MySQL from +1 to +43
  • How to query non duplicate rows but keep the primary key
  • How to group all scores by username in a table and display rank?
  • How to remove timestamp with the date using sql query?
  • Modify SQL query to get data only from past 2 months
  • change engine type from MyISAM to InnoDB
  • Need mysql query to find total sum as per matched query
  • json_table syntax and structure
  • Is there any way in sql query to rank certain value in ordered set of records such that ranking reset after it encounter
  • not able to login in google cloud storage sql instance
  • Inserting data into MySQL database with VBA
  • Make (user_id&music_id) unique in Likes table and Dislikes table
  • Can't do 50k insertion using bulkCreate
  • SQL syntax to get data IN date
  • How to get 4-byte emojis to store correctly in mysql 5.7?
  • How to create an in-memory database for PHPUnit testing?
  • How to write an SQL query to fetch the id in a column that has all the required data in some other column?
  • Invalid COLUMN in the select list because it is not contained in either an aggregate function or the GROUP BY clause
  • How to get actual index number of rows in MySQL when using Where clause
  • SQL Table Relationship
  • How to fix "FUNCTION doesnt exist" error in mysql?
  • Importing .bak from MSSQL into MySQL database
  • Aggregate query with distinct subquery kills every connection
  • How do i use and with multiple or condition?
  • How to fix 'The server requested authentication method unknown to the client [caching_sha2_password]' on docker compose?
  • In Mysql How to fetch distinct row value from a column1 and sum(column 2 row value) group by invoice number
  • MySQL ORDER BY field with a toggle
  • How to insert system's Date into Table
  • Aws lambda function too many connection issue rds
  • MYSQL - join 2 tables - sort in time
  • SQL code to select records where field_A matches the field_A value of a certain field_B value
  • How to model Amazon Redshift external table for changing column structure?
  • Query the list of CITY names from STATION that do not start with vowels and do not end with vowels. Your result cannot c
  • how to get rows with time for each minute for last 30 minutes
  • SQL Query With Between ,AND & OR in Where Clauses Not Given Correct Answers
  • ER_PARSE_ERROR: You have an error in your SQL syntax;
  • How to write normal sql queries inside controller in laravel
  • MySql query between range values(single record)
  • Set length conditions on text column in MySQL table
  • Mysql order of sorting
  • Sequelize raw queries TextRow and getting data out of it
  • Comparing the contents of two csv files, where the relation between the two files is specified in a third file?
  • Can 1 primary id link to multiple ids in one table?
  • Rewrite MySQL query and make it short
  • Why are parentheses used in JOIN USING clauses
  • Delete from row to row in phpadmin
  • Inserting Data into a table
  • How to efficiently get a entry with highest version in mysql table
  • How do I create views in dolphindb?
  • MySQL - update with inner join is creating nulls
  • Should i rather use a subquery or a combined WHERE?
  • INSERT INTO using data from other table causing ERROR 1064
  • How to display a MySQL table completely?
  • Can create procedure without using delimiter in MySQL?
  • distributed crawler and consistency
  • How to use mysql in node with Typescript
  • Is there any collation to differentiate 'е' and 'ё' in case insensitive way?
  • How to grant all privileges to specific user to remote ip
  • MySQL - Why is Safe Update Mode blocking this UPDATE command?
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com