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

How to delete all but keep N latest rows per product in price history table

I have a table in MySQL 5.7.26 where I am storing history of products, I do not have any auto increment column +------------+---------------------+ | product_id | date_in | +------------+---------------------+ | 500 | 2020-04-24…
Umair Ayub
  • 19,358
  • 14
  • 72
  • 146
1
vote
3 answers

Get other columns(attribute) with max values

my dataframe ID COURSE_ID SEC_ID SEMESTER YEAR GRADE 00128 CS-101 1 Fall 2009 A 00128 CS-347 1 Fall 2009 A- 12345 CS-101 1 Fall 2009 C .... I want to get course_id & sec_id with the maximum number…
beenz
  • 47
  • 5
1
vote
2 answers

show only latest date record for each day

I have a table as follows ID apiDate totalCases 1 2020-04-18 12:00:00 10 2 2020-04-18 12:00:00 15 3 2020-04-19 12:00:00 20 4 2020-04-19 12:00:00 25 5 2020-04-19 12:00:00 …
troshan
  • 134
  • 1
  • 11
1
vote
1 answer

More efficient way to SELECT rows from PARTITION BY

Suppose I have the following table: +----+-------------+-------------+ | id | step_number | employee_id | +----+-------------+-------------+ | 1 | 1 | 3 | | 1 | 2 | 3 | | 1 | 3 | 4 | | …
Jordan
  • 868
  • 1
  • 8
  • 25
1
vote
3 answers

SQL - get MIN value of row and check this MIN value to be in row at least 2 times

What I'm trying to achieve is this: 1) Get the minimum value of a salary in the table for every department. 2) If this minimum value exists in the table at least two times for every department, then show its department id. Example: column1 name …
user3719859
  • 25
  • 1
  • 1
  • 5
1
vote
1 answer

Select the last entry recorded in a table for each day, within a duration of days

How can I select the last entry recorded for each day? In this example, I need the last item number ordered and the last DateOrdered entry for each day over the last 5 days. Here's my table: ItemNumber | DateOrdered 1 2020-04-01…
Shane
  • 522
  • 1
  • 6
  • 22
1
vote
1 answer

How to fetch unique most recent items using IN from table in PostgreSQL

Scenario +--------+-------+------------+ | item | price | date | +--------+-------+------------+ | apple | 31 | 2019-09-11 | | banana | 11 | 2019-09-11 | | banana | 13.23 | 2019-10-12 | | apple | 30.1 | 2019-10-12 | | banana | 12.77…
Abhi9
  • 320
  • 1
  • 2
  • 13
1
vote
2 answers

Is there a way finding highest value by more than one grouping

I am trying to find the max value of grouping two columns together. I am trying to find the busiest hour in a day, given a month. SELECT date_part('day', tpep_pickup_datetime) AS trip_day, date_part('hour', tpep_pickup_datetime) AS…
king_sules
  • 39
  • 8
1
vote
1 answer

Get records with latest record using row number and partition using joins

Here is my SQL Server table: DECLARE @EMP TABLE ( Id INT, NAME VARCHAR(200), AlarmOnTimeStamp DATETIMEOFFSET, AlarmOffTimeStamp DATETIMEOFFSET NULL ); DECLARE @EMPCOMMENT TABLE ( EmpId INT, Comment…
t-bolt
  • 21
  • 1
  • 5
1
vote
1 answer

Get top item for each year

I have a datatable with some records. Using mysql I am able to get a result grouped by a specific period (year) and users and ordered (in descending order) by number of species. SELECT YEAR(entry_date) AS period, uid AS user, COUNT(DISTINCT pid) AS…
Michal Stružský
  • 51
  • 4
1
vote
1 answer

Select all columns grouping by version - Postgres

I need to query all columns in a table of all customers, the main factor being the latest version for each customer. My table: My Query: SELECT DISTINCT ON(code) code, namefile, versioncol, status FROM table_A ORDER BY…
rufus05
  • 43
  • 1
  • 10
1
vote
2 answers

How do I grab each student’s 3rd max assignment mark in each subject

I am trying to write an sql that will allow me select each student’s 3rd best assignment mark in each subject. I have tried with the query below but it isn't working for me. I will be grateful to get some answers. I am getting an error [Code: 0, SQL…
Ekaku509
  • 17
  • 6
1
vote
3 answers

Get most recent measurement

I have a table that has has some measurements, ID and date. The table is built like so ID DATE M1 M2 1 2020 1 NULL 1 2020 NULL 15 1 2018 2 NULL 2 2019 1 NULL 2 2019 NULL 1 I would…
Edi Itelman
  • 423
  • 5
  • 14
1
vote
3 answers

SQL- Return rows after nth occurrence of event per user

I'm using postgreSQL 8.0 and I have a table with user_id, timestamp, and event_id. How can I return the rows (or row) after the 4th occurrence of event_id = someID per user? |---------------------|--------------------|------------------| | …
Indy
  • 75
  • 6
1
vote
1 answer

Impala SQL return top 3 grouped records

I have this table example on impala.I want to get max top number_of_call for each caller caller and group_id are unique grouped together caller | group_id |number_of_call 23 | 4433 | 12 45 | 342 | 5 23 | 475 | 33 45 | …
Beyhan Gül
  • 171
  • 2
  • 11
1 2 3
…
99 100