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
2 answers

Mysql get target row number ±2 rows

I have a My_Table that looks something like this: PK1 | PK2 | Value | Date_Changed And a query that does essentially this: SELECT PK1, PK2, Value, ROW_NUMBER() OVER ( ORDER BY Value desc, Date_Changed ASC) AS position FROM My_Table …
brahtala
  • 21
  • 3
1
vote
1 answer

SQL Query for DynamicsCRM Most recent activity over all contact per account

I want to list the most recent activity (from FilteredActivityPointer) per account (FilteredAccount). Problem is that activities are not directly linked to account but have contacts in between. So accounts can have multiple contacts. Multiple…
HHeckner
  • 4,722
  • 4
  • 23
  • 33
1
vote
3 answers

How do I group and order with sql at the same time?

I have got the following sqlite3 table: Name | LastUpdated | Status ============================ Adam | 2011-05-28 | 1 Bob | 2011-05-05 | 6 Adam | 2011-05-27 | 2 Adam | 2011-05-16 | 1 Adam | 2011-05-26 | 3 Bob | 2011-05-18 | 1 Adam |…
Rudacles
  • 183
  • 1
  • 1
  • 6
1
vote
1 answer

Posgresql SELECT first x rows by date and specific id

I have this table : +----+--------+--------+-----------+----------------------------+ | id | type | amount | dealer_id | date | +----+--------+--------+-----------+----------------------------+ | 12 | sub_s | 29.00 | …
IndiaSke
  • 348
  • 1
  • 2
  • 10
1
vote
1 answer

Postgres SQL: How to select the highest value in the "media" column for the same time stamp in postgres?

I am using possql through the DataGrip program. I have the following table: timestamp | Channel | media ----------------------------------------- 2020-04-29 00:00:00 | 3 | 1.2 2020-04-29 00:00:00 | 4 | 2 2020-04-29…
Guilherme Alves
  • 55
  • 4
1
vote
1 answer

Top-N By Decade for successive decades (in SQL Server)

I'm trying to get a ranked list of Top 5 (i.e. most common) document titles, grouped by decade, for each of the 6 most recent decades. The document titles are non-unique. There could be dozens or even hundreds of documents with the same title in…
BRW
  • 187
  • 1
  • 10
1
vote
2 answers

Row sorting and selection logic in Python on Sqlite db

hello Thanks for taking the time to go through my question. I work in the budget space for a small city and during these precarious time, I am learning some python for maybe in the future helping me with some financial data modelling. We use SAP…
toprockupbeat17
  • 47
  • 5
1
vote
2 answers

Get the latest child messages and also parent messages that are childless

The following is the Message model class Message < ApplicationRecord belongs_to :parent_message, class_name: 'Message', optional: true has_many :child_messages, foreign_key: :parent_message_id, class_name: "Message" has_many…
Swamy g
  • 2,106
  • 4
  • 23
  • 35
1
vote
2 answers

SQL - Selecting records by the newest date for each record

I have a table with some columns, amongst which there are two - number, joining_date. What I want to select is the newest joining date and matching number. I created the following script: SELECT ac.number, ac.joining_date FROM accounts ac …
Forin
  • 1,549
  • 2
  • 20
  • 44
1
vote
2 answers

Querying single oldest record for each ID

Assume that I have a database with the columns foo, bar, creationdate and data. What I need, is a query, that queries the the first row that was created (i.e. where creationdate is the lowest) for each unique combination of foo and bar. For example,…
Lehks
  • 2,582
  • 4
  • 19
  • 50
1
vote
1 answer

How to filter by comparing the dates in MySQL Workbench

I have a table look like this: ID B C date01 date02 date03 11 xxx xxxx 2020-05-01 2020-05-02 2020-06-02 11 xxx xxxx 2020-06-01 2020-06-03 2020-05-02 11 xxx xxxx 2020-07-01 2020-07-03 …
wawawa
  • 2,835
  • 6
  • 44
  • 105
1
vote
1 answer

Grab two latest records of group by platform and semver

For every pairing of platform and semver I'm trying to to get the second newest record as a way to support the previous app version to latest, at all times. Here's my schema: id, platform, semver, name Some sample data: I would like the query to…
Some dood
  • 85
  • 5
1
vote
1 answer

SQL in MS Access max of sum

SELECT tableResults.PoliticalParty, MAX(PoliticalPartyVotes.TotalVotes) AS [EX11] FROM (SELECT tableResults.PoliticalParty, SUM(INT(tableResults.Votes)) AS TotalVotes FROM tableResults GROUP BY…
מתן חבר
  • 13
  • 2
1
vote
5 answers

Getting single row from JOIN given an additional condition

I'm making a select in which I give a year (hardcoded as 1981 below) and I expect to get one row per qualifying band. The main problem is to get the oldest living member for each band: SELECT b.id_band, COUNT(DISTINCT a.id_album), …
Bardinator
  • 11
  • 1
1
vote
1 answer

How to make a df with the minimum value of each column and the corresponding value of other column?

I have a df like this: '62' '63' '64' code 0 20.03743333333333 26.869833333333325 24.724533333333333 82022 1 10.4041 31.236499999999996 29.091200000000004 82026 2 …
Javier
  • 493
  • 3
  • 15
1 2 3
…
99 100