Stack Overflow
Stack Overflow

Questions tagged [greatest-n-per-group]

Query the row with the greatest/least value per group.

One of the most frequent stumpers for SQL programmers is to query rows that match the greatest (or least) value over a given group. For example, all details about the City with the largest population per state. You can't do it simply with GROUP BY because the other columns will be ambiguous.

A variation that appears occasionally is to query the top n rows from each group (see the ).

Make sure to include the database tags as well (, , etc.) because depending on the database, the optimal query might be different.

4224 questions
1
vote
3 answers

Get The Most Recent Record

In SQL Server, I am using two tables (AOC_MODEL and PACKAGE that I have joined together. I want to get the most recent version from the I_FW Column for every AOC_ID. SELECT AOC_MODEL.AOC_ID, AOC_MODEL.CONTROLLER, …
jbegic
  • 59
  • 7
1
vote
2 answers

Selecting MAX() on a group doesn't return the corresponding sibling columns

I'm using MySQL Tutorial's sample database. I need to find the sales person with the worst sales (lowest venta_por_empleado) and the best sales (highest venta_por_empleado) per each semestre and city. Using temporary tables I obtain the following…
chepe263
  • 2,774
  • 22
  • 38
1
vote
1 answer

SQLAlchemy: How to use group_by() correctly (only_full_group_by)?

I'm trying to use the group_by() function of SQLAlchemy with the mysql+mysqlconnector engine: rows = session.query(MyModel) \ .order_by(MyModel.published_date.desc()) \ .group_by(MyModel.category_id) \ .all() It works fine…
Mr. B.
  • 8,041
  • 14
  • 67
  • 117
1
vote
1 answer

Oracle Sql: Select only the latest records by an id and a date

My table looks like this: ID FROM WHEN 1 mario 24.10.19 1 robin 23.10.19 2 mario 24.10.19 3 robin 23.10.19 3 mario 22.10.19 I just want the newest records from an ID. So the result…
DropMania
  • 123
  • 1
  • 11
1
vote
1 answer

Pandas groupby with identification of an element with max value in another column

I have a dataframe with sales results of items with different pricing rules: import pandas as pd from datetime import timedelta df_1 = pd.DataFrame() df_2 = pd.DataFrame() df_3 = pd.DataFrame() # Create datetimes and data df_1['item'] = [1, 1, 2,…
Andrzej Wodecki
  • 107
  • 1
  • 8
1
vote
3 answers

First record combined with GROUP BY

Let's say I got a table "values" which contains the fields id (int) name (varchar) value (float) timestamp (int) Now I want to to calculate the highest lowest and first value (timestamp based) for each name on the entire values table. Is this…
Gilles Lesire
  • 1,237
  • 17
  • 33
1
vote
1 answer

MySQL: Combining the MAX method with GROUP BY Clause ERROR 1055

I have a table containing the monthly sales of a car-salesman who sells 3 different brands. I want to create a query which returns a table of the year, month and best selling car brand with the amount sold for that month. A portion of the table is…
RickPeck
  • 165
  • 2
  • 14
1
vote
2 answers

How to select rows where a specific column has the highest value and another column has a specific value

I am looking to select the following rows from a table where the following constraints are met: I have been messing with joins on itself (selecting max version of this if idb = x in a subquery) but have had no luck For example: idb = x if va of a…
Dave L
  • 29
  • 1
  • 2
1
vote
3 answers

How to pull the latest records added in a table?

I want to pull the records associated with the latest VERSION_ID. FILE_EXTRACT_VERSION table looks like this: VERSION_ID VERSION_TAG RELEASE_DATE 1 1.0.0 2019-10-15 11:49:02.283 2 2.0.0 2019-10-15…
Yashvardhan Rathi
  • 49
  • 5
1
vote
4 answers

First date from second table where date is bigger than date in table first oracle

I want pull out the date from second table. My second table may have a over 100 dates to a unique record(A,B,C). I have I first Table : Name Count Data1 A 1 20190201 B 3 20190201 C 6 20190201 and the second table : Name Date A …
Błażej
  • 125
  • 2
  • 11
1
vote
1 answer

Return the record with the date closest to but greater than now NOW() in table b, for each record in table a

Server Version: 10.3.15-MariaDB-log I have a data structure like this TABLE A - Participant participantID -------------- 1 2 3 4 TABLE B - Appointment appointmentID | participantID | locationID |…
Xenology
  • 2,357
  • 2
  • 21
  • 39
1
vote
2 answers

Select top 15 records from each group

I want to select 10 merchant accounts, pick top 15 transaction records for each merchant account with page size like 10*50 ? I have this query which gives me the top records, where I need a fix to pick the "top 15 records for each merchant…
Balaji Birajdar
  • 2,394
  • 1
  • 23
  • 29
1
vote
2 answers

SQL - Pull distinct row based on max value

I am trying to pull the most recent sale amount for each salesperson. The salespeople have made a sale on multiple days, I only want the most recent one. My attempt below: SELECT salesperson, amount FROM table WHERE date = (SELECT MAX(date) FROM…
franklingranger
  • 35
  • 4
1
vote
1 answer

How can I grab top selling product for each month using sql?

Given a table with monthly transactions (customer id, month, payment) and a table with customer info (type 2 dimension) (id, cust_id, plan type, num users, start_date, end_date): What is the top grossing plan each month (month, $, plan)? My answer…
Drew Pham
  • 89
  • 7
1
vote
1 answer

Avoid repeating expression in SELECT list and WHERE clause

I have a basic query that fetches rows from data dumped from google autocomplete containing the place_id, formatted address and a json column which contains the entire json object. I believe I am using Postgres 10 or 11. WITH t AS ( …
GrandFleet
  • 809
  • 2
  • 10
  • 25
1 2 3
…
99 100