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

SQL -- Display 2nd highest salary for each dept, if employee having same salary then display salary with least empno

I need to display 2nd highest salary for each dept, if employee having same salary then display salary with least empno . I have emp table as below in which deptno 20 have 2nd highest salary 3000 for two empno 7788 and 7902 . EMPNO ENAME JOB …
iteng
  • 15
  • 1
  • 5
1
vote
4 answers

Retrieve 2nd highest count by each group

I have a table like this: shopID supplier supply_count 1 a 12 1 b 10 1 c 8 1 d 7 2 b 12 2 f 12 2 e 10 3 b …
Anthony5
  • 25
  • 3
1
vote
3 answers

pick all positive least numbers from data set

I have below data in a table ID AMOUNT DAYS 1 10 1 1 20 2 1 30 3 1 1 4 2 34 1 2 234 2 2 234 3 2 34 4 3 3 1 3 3 2 3 23 3 3 20 4 I want below results as all amounts which have least days of a ID ID AMOUNT …
Adeel
  • 75
  • 1
  • 10
1
vote
4 answers

simple sql (i hope!)

Given a data set similar to this: ccy | time | fxrate GBP | 11 | 1.2 EUR | 21 | 1.4 CHF | 9 | 3.1 GBP | 15 | 1.1 EUR | 20 | 1.5 CHF | 1 | 3.0 CHF | 7 | 3.0 GBP | 20 | 1.9 I want to get the latest fxrates (by 'time') for each…
pomo
  • 2,251
  • 1
  • 21
  • 34
1
vote
1 answer

Mysql slow performance with group by order by

I am using Mysql 5.7 I have table which having 7006500 rows. My query performing group by and fetching row which has maximum count with each group on column which is already indexed but still takes time for execution. Below is my query,execution…
5a01d01P
  • 663
  • 2
  • 9
  • 20
1
vote
2 answers

MySQL 8.0.15 Dam slow select query

Motto of the query is very simple, to find out the last entry on a foreign key column. the pseudo code I can say is select vehicleid , last_journey_point , last_journey_time from journeyTable. here is my SQL statement -- loconumber is a indexed…
balu
  • 81
  • 3
1
vote
3 answers

Symfony Doctrine QueryBuilder - Get the latest published news of each category

So i've seen many subjects about my issue (greatest-n-per-group), but it seems like i just can't make it work with the queryBuilder! (for example : post) I'm running symfony 4.2.2 Entities ------------- ------------- News …
Elbarto
  • 1,213
  • 1
  • 14
  • 18
1
vote
2 answers

Order result form Mysql group by

Ok i got this table +-----+-----------------------------+-------------+-------------+----------+---------+ | id | title | created | updated | category | content…
slier
  • 6,511
  • 6
  • 36
  • 55
1
vote
1 answer

Make random groups of students from database based on grades

I have table with 30 students with grades from 1 to 5. How to make 6 different students group which contains five randomly selected students? Each group must have five students and each of those five students must have different grade. First…
Feuerbach
  • 15
  • 3
1
vote
1 answer

Select the 2 highest count(value) by year

I have a postgresql-9.6 database, let's name it sales, similar as following : sale_id customer_id sale_date price 1 20 2017-01-05 2000 2 150 2017-05-26 1500 3 121 2017-07-07 2560 4 …
SpK
  • 23
  • 5
1
vote
1 answer

Finding Last Record Per Group with Multiple Aggregate Functions- SQLite

I have a table ("trds") that contains fields RefDate, Symbol, Timestamp, Sequence, Quantity, Price, SaleCondition, Pid, and Submkt ("select * from trds limit 100"): RefDate Symbol Timestamp Sequence Quantity Price SaleCondition Pid SubMkt 1 …
Kramerica
  • 29
  • 4
1
vote
3 answers

SQL for getting data from the row with the highest version value

I have a table with data like this: Id | Version | isLive -------+---------+------- comp1 | 2 | true comp1 | 3 | true comp1 | 4 | false comp1 | 1 | true comp2 | 4 | true comp2 | 1 …
codeNinja
  • 1,442
  • 3
  • 25
  • 61
1
vote
1 answer

finding the highest scoring home team for EVERY SEASON

Below is my hive query trying to find out the highest scoring home team for every season. select t1.season , max(t1.TOTAL_Goals) as Highest_Score from (select season, home_team_id, sum(home_goals) TOTAL_Goals from game_kpark group by…
Keon Park
  • 53
  • 1
  • 4
1
vote
3 answers

Select first entry with rev_parent_id=0 from joined tables

another one to the #greatest-n-per-group party! My previous code: select count(*) from revisions join files on rev_file = file_id where rev_parent_id like 0 and rev_timestamp between '20011231230000' and '20191231225959' and file_namespace…
aleskva
  • 1,644
  • 2
  • 21
  • 40
1
vote
3 answers

SQL query to show most recent qty, but grouped by customer

Item Number | Customer | Creation Date | Onhand Qty 123 1 03-FEB-19 654 234 3 03-FEB-19 987 789 5 03-FEB-19 874 …
LarryGythgoe
  • 13
  • 3
1 2 3
…
99 100