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
1 answer

Join top n records from child table

I am struggling to figure out how to select only the first 4 records from a child table per record in the parent table in a master-detail relationship. Tables example: Product ProductImage --------- ------------------- Id | Name …
Leonard
  • 348
  • 1
  • 6
  • 20
1
vote
1 answer

PostgreSQL partition by and select first row with specific column value

I have data in the following format: ID DATE METRIC 1 1/1/19 1 1 1/3/19 1 1 1/5/19 0 2 1/2/19 0 2 1/9/19 0 2 1/11/19 0 3 1/1/19 0 3 1/2/19 0 3 …
user3249770
  • 329
  • 2
  • 7
1
vote
1 answer

mySQL Query: How to choose just one comment from post list?

Table post: id Table post_comments id, post_id, comment, created_at REQUEST: SELECT * from post left join post_comments on post_comments.post_id = post.id This request return all of the comments for every post. How could I limit this query for…
mvn.2047
  • 362
  • 1
  • 10
1
vote
1 answer

How to get latest records based on two columns of max

I have a table called Inventory with the below columns item warehouse date sequence number value 111 100 2019-09-25 12:29:41.000 1 10 111 100 …
rajini
  • 33
  • 4
1
vote
3 answers

Group by return correct name for a max value in another field

Sorry for the poorly titled question but I have the following example data: Account ID Name Order Value 1122 John 2000 1122 Dave 1500 For many 000's of accounts (in my learning example). What i am trying to do…
andre1990
  • 107
  • 2
  • 9
1
vote
1 answer

Take only one record from many results when one columns contain same value in different rows

I have a my SQL results similar to below. I have been using rails and would like to have solution either in SQL or Rails id | name | addr | add_id ------------------------- 1 | m | Q | 15 2 | n | Q | 3 3 | o …
ZZzzZZzz
  • 1,800
  • 3
  • 29
  • 51
1
vote
3 answers

Group by with selecting max per group part

This is a ClickHouse request, but I think i'm missing some core sql solution. I want to count maximum number of rows per minute during each day: SELECT date_date, formatDateTime(date_time,'%F %H:%M:00', 'Asia/Dubai') AS Max_TPS_Time, …
Andrey Dovgalyuk
  • 49
  • 5
1
vote
5 answers

MySQL: How to order the data in each group?

I have a table like this: | Key | Value | Message | |-----|-------|---------| | a | 1 | xx | | a | 2 | yy | | b | 5 | mm | | b | 4 | nn | I would like to group the data by Key, get the minimum Value of…
Ken Zhang
  • 1,454
  • 2
  • 13
  • 27
1
vote
3 answers

How to join the result of a request to another table (to add column)

Edit I have two tables : etape and etapex. I need to find the maximum idProjet for each idEtape (in table etapex), and there link the idEtape and idProjet to 'nomEtapeandlivrable` from the table etape. So I tried two different things : SELECT…
Natacha BK
  • 137
  • 1
  • 8
1
vote
2 answers

Distinct by specified columns but select all

I was hoping for some help on a query I am stuck on. Basically I have the following table id | tmpid | tmpyear | tmpone | tmptwo | tmpthree | tmptype 1 | 2 | 9 | 33 | 26 | 12 | profit 2 | 2 | 9 | 32 | 10 …
Fatalerror
  • 23
  • 3
1
vote
1 answer

Efficiently get IDs with a given status from table of status updates

I have a SQLite table that records a history of status updates with columns TaskID, Timestamp, and Status. Each time the status of a task is updated, I add a row to the history table describing the new status. The current status of a task is defined…
Sam
  • 129
  • 1
  • 2
  • 7
1
vote
1 answer

Select top first row for each group based on multiple order criterias

Disclaimer: I have searched for similar questions but I haven't found a clear answer to my issue. I have a table like this: id | catid | views | text 1 100 2000 "sometext" 2 200 2000 "sometext" 3 200 3000 …
giò
  • 3,402
  • 7
  • 27
  • 54
1
vote
2 answers

Last record per item per group

Simplified problem: In a table "table1" we have columns: RowID, ItemID, BranchID, RoomID, Date, Qty I am trying to retrieve the last Qty of an ItemID in each RoomID in each BranchID. Once I get this going the plan is to JOIN table1 to ItemIDTable,…
anm767
  • 59
  • 6
1
vote
3 answers

SQL : Select the latest sale for each product

I'm coming to you with a problem that's been bugging me for a while. I have this database, Registering every sales made by a shop, the columns are such : The product code is unique to every item (a barcode basically), and I want to extract the…
Cynass
  • 55
  • 5
1
vote
3 answers

How to get the latest rows for each user ID in laravel?

id user_id name qty datetime --- --------- ---- ---- ----------- 1 1 a 5 2019-12-01 12:26:01 2 2 b 3 2019-12-13 12:26:02 3 1 c 4 2019-12-13 12:26:03 4 2 …
Akshay Kumar
  • 5,740
  • 2
  • 12
  • 19
1 2 3
…
99 100