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

Select top 2 sales in each class SQL

I have the table which I want select only top 2 sales in each class, the result is Sophia A 40 Jennifer A 15 Greg B 50 Jeff B 20 Stella B 20
KEVINBOSS
  • 149
  • 1
  • 1
  • 11
1
vote
2 answers

PostgreSQL: Returning the most recent rows grouping by a foreign key column

I have a table that looks similar to this: credit +---------+----------------+-------------+--------------+-------------------------+ | id (PK) | person_id (FK) | transaction | total credit | date_time …
ty2k
  • 761
  • 5
  • 12
1
vote
2 answers

How to find the top or max Column value of a table?

Having the following table which is some kind of an http request logs: +---------+----------+-----------+----------+ | County | Mobile | OS | Browser | +---------+----------+-----------+----------+ | USA | iPhone | iOS |…
Adelin
  • 18,144
  • 26
  • 115
  • 175
1
vote
2 answers

How to extract the first 5 rows for each id user in Oracle?

I have only one table where I want to extract the first 5 rows for each ID_USER. I execute the following query: SELECT ID_USER as U_ID_USER, COUNT(ID_USER) as NUM_TIC FROM TABLE_USERS GROUP BY ID_USER ORDER BY ID_USER Which returns…
Y2KSAVE
  • 55
  • 5
1
vote
2 answers

How to include column which is not a part of group by

How to get max of w_cost by v_id and also the final result set should include av_id. s_id sg_id r_cost w_cost av_id v_id 123 100 0.50 1.00 1 333 123 105 0.75 0.50 2 333 123 330 2.00 Null 3 888 If w_cost…
sandy
  • 103
  • 2
  • 9
1
vote
4 answers

sql select with group by and join for lookup

Imagine I have a product table and an orders table. I want to list out the most recent order for each product I imagine something like this select name, description, price, max(date) from product join order on order.item = product.name group by…
pm100
  • 48,078
  • 23
  • 82
  • 145
1
vote
2 answers

How to grab the DATE that the MAX occurred?

So i have a table called Value. This table has columns called: VALUE_ID, VALUE, HR, VALUE_TYPE. I am trying to grab not only the maximum value but also the HR (and ultimately the day) that the Maximum Value Occurred. Below is some sample…
John Wick
  • 703
  • 10
  • 22
1
vote
1 answer

Select Duplicate Row with Custom Amount Oracle

In a [PERFORMANCE] table, some rows have the same value for the PERFORMANCE_INDICATOR column. ID | PERFORMANCE_INDICATOR | REALISASI | --------------------------------------------------------- 1 | Compliance of Inventory Data…
Iqro
  • 41
  • 1
  • 7
1
vote
2 answers

Conditional selection in SQL

suppose we've got the following table: A B C Year ----- ---- ---- ----- ABC Val1 Val2 2015 ABC Val3 Val4 2016 ABC Val6 Val7 2017 ABC Val8 Val9 2018 XYZ Val10 Val9 2016 XYZ…
RAKY
  • 51
  • 2
1
vote
2 answers

Filter a join on each primary-foreign key relation only

I am using MySql. I have table job that has a primary key job_pk_id and the table stores details of every job. Also I have a table job_running_status where job table's job_pk_id is a foreign key and this table basically contains records of when a…
DockYard
  • 989
  • 2
  • 12
  • 29
1
vote
1 answer

MySQL Get Latest Entry For All Stores

I am trying to get the latest rows from a table that records the cumulative sales total for each 15 minutes. The basic layout of the table is StoreID, Time, Sales, Tax, Date I'd like to be able to return the latest records for each store id. The…
Jarrett Duskey
  • 63
  • 6
1
vote
1 answer

Greatest-n-per-group; multiple ways of handling ties?

Quite often I have to find a MAX/MIN value within a group. There are numerous solutions to this problem, but one thing they don't seem to account for is handling ties. For example, take a dataset with the following records: category | product …
daje
  • 40
  • 5
1
vote
1 answer

Finding the rows with same values having same data in sql using java?

From the table as I want to get the rows contains minimum value of works as here is 3 so that I can get the rows of id 2 and 5 as both the rows has same minimum value. ID | emailID | …
Mutaealim
  • 127
  • 11
1
vote
2 answers

Symfony Query Builder how to get last grouped records

I have an entity named "Location" with the following fields: id (auto increment) vehicle (foreign key to "vehicle" table) lat lng speed I need to get the last locations (with last id) grouped by vehicle using doctrine Query Builder. Here is my…
BERROUBA MOHAMED EL AMINE
  • 51
  • 5
1
vote
1 answer

Fast top N by count by group in data.table

I'd like to know the preferred way to frank subgroups on the count of their appearances by group. For example, I have customers who belong to segments and who have postal codes. I would like to know the most common 3 postal codes for each…
C8H10N4O2
  • 18,312
  • 8
  • 98
  • 134
1 2 3
…
99 100