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

Aggregate first order data to all orders - Microsoft SQL Server

This is for Microsoft SQL Server. I'm trying to select the first purchase for each customer in a table similar to this: transaction_no customer_id operator_id purchase_date 20503 1 5 2012-08-24 20504 1 …
Aaron Broache
  • 11
  • 2
1
vote
5 answers

How to filter rows based on columns with maximum value?

I have a table as below --------Table 1---------- ID VERSION STATUS 001 1 1 001 1 2 002 1 3 002 2 4 002 2 3 So here I need to filter the ID's based on the…
Arun Elangovan
  • 237
  • 1
  • 3
  • 16
1
vote
4 answers

Fetching latest records of individual by joining 2 tables

I have to fetch the latest record of the student which derived by joining 2 tables: table 1: table 2: id name id marks EXAM attended time status -------- ---------------------------------------------- 1 ABC 1 …
balaji
  • 118
  • 1
  • 8
1
vote
4 answers

How to find the three greatest values in each category in PostgreSQL?

I am a SQL beginner. I have trouble on how to find the top 3 max values in each category. The question was "For order_ids in January 2006, what were the top (by revenue) 3 product_ids for each category_id? " Table A: (Column…
Ricky C
  • 33
  • 5
1
vote
2 answers

ten cheapest room for each city

I have 3 table Room, Hotel, City, how can i select ten cheapest room and its hotel_id for each city possible to execute the subquery for each city? SELECT price, room_id, hotel_id, city_id FROM Hotel JOIN Room USING(hotel_id) ORDER BY…
eugenes
  • 25
  • 1
  • 7
1
vote
2 answers

Value from previous row in GROUP BY as column

I have this table: +----------+-------------+-------------------+------------------+ | userId| testId| date| note| +----------+-------------+-------------------+------------------+ | 123123123| …
Robin71
  • 383
  • 5
  • 26
1
vote
2 answers

sql for top 5 records from second table in two joined tables

i have 2 tables. student and student_records. student table has S_id and has foreign key in student_records. When joining these tables, I need 1 student ID and top 5 records from student_records table for that Student ID. 1 abc 1 dedw .. .. 2 def…
lkeo
  • 11
  • 2
1
vote
2 answers

Joining most recent record based on the date in another record

I have two tables - let's call them week and contract, like so: Week Contract emp_id | starting | data1 | ... emp_id | from_date | data2 | ... -------|------------|-------|-- …
Merus
  • 8,796
  • 5
  • 28
  • 41
1
vote
2 answers

MySQL SUM TOP 2 RECORDS FOR EACH CATEGORY

I have a table for students scores, am trying to sum top 2 marks for all student for a particular category.I have search for similar post but have not gotten correct answer I have tried summing the marks but am only getting result for two students…
DUNCAN OSUR
  • 19
  • 1
1
vote
2 answers

Efficiently selecting distinct (a, b) from big table

I have a table with around 54 million rows in a Postgres 9.6 DB and would like to find all distinct pairs of two columns (there are around 4 million such values). I have an index over the two columns of interest: create index ab_index on tbl (a,…
bm1729
  • 2,315
  • 3
  • 21
  • 30
1
vote
1 answer

How to select newest records from table with many-to-many relationship

I have two tables.Table Products in which there are two product types ProductType_1 and ProductType_2, and second table ProductType_2_Items in which I keep the information that ProductType_1 is contained in ProductType_2. I need to get the latest…
Conrad.B
  • 21
  • 5
1
vote
1 answer

How to get second highest salary department wise?

Suppose we have some employees in each department.we have total 3 departments . Below is the sample source table named 'employee' emp dept_id salary A 10 1000 B 10 2000 C 10 3000 D 20 7000 E 20 9000 F 20 …
Sayem
  • 104
  • 11
1
vote
3 answers

How to join tables with non-equal condition?

I’m trying to query all the users with their first successful app install with the app version details. So I tried to query users’ first successful install, then compare the installed_date with latest app version before installed_date. SELECT…
Jackie
  • 307
  • 2
  • 14
1
vote
2 answers

MariaDB Query using IN with LIMIT by row

I am having some difficulty trying to use LIMIT in one of my Sub Queries, I ran into the error MariaDB doesn't yet support 'LIMIT in subquery' On a query that is similair to what I've done in PostgreSQL in order to get 3 child results for every…
TheLovelySausage
  • 3,838
  • 15
  • 56
  • 106
1
vote
1 answer

Select with limit for the same id

i have 3 tables Posts_comments comment_id, comment_post_id, comment_value, comment_time comment_user_id 1 1 test DATETIME 1 2 1 test2 DATETIME 2 3 2 …
Patryk Tchórzewski
  • 125
  • 2
  • 16
1 2 3
…
99 100