logo
down
shadow

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


Comments
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:
MyModel.where("place_id=?",place_id).
        group("place_id,user_id").order("count(user_id) DESC").first
MyModel.select("DISTINCT(place_id)").
        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')
    .joins(:datapoints)
    .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
    FROM FOO
    GROUP BY ProjectName, Origin, week)
SELECT A.ProjectName, A.Origin, A.Week, A.MyCount
FROM CTE A
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...
SELECT x.* 
  FROM my_table x 
  JOIN 
     ( 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