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

Group by while discarding old data in query

I have a table (Oracle): price -------- integer id (pk) integer part_id (fk) number amount timestamp change_timestamp And whenever a part price is changed the software (not mine) adds a new row to this table. Instead of altering the data and there…
ryvantage
  • 13,064
  • 15
  • 63
  • 112
1
vote
1 answer

SELECT Statement Involving Three Different Tables

Note: I was unsure how to title this, if anybody has a better idea feel free to change it. My database currently has the following tables (some columns and other details have been removed from the output of the commands shown, but they should not…
DreamConspiracy
  • 343
  • 2
  • 11
1
vote
0 answers

How to SELECT rows with MAX(column value), distinct with another column AND TABLE?

I know there was another thread asking how to select max of one column distinct with another column. However, I need code which selects max of one column distinct with another column and another table. All the examples creating a t1 and t2 do not…
daekimtx
  • 11
  • 1
1
vote
1 answer

PostgreSQL - Select row with composite maximum value from 2 columns

I would like to select the best offers for a merchant in PostgreSQL 9.6 according some simple rules: An offer is better than another if its discount value is greater regardless the benefit type If the discount values are equal, then the one with…
Andras Szoke
  • 125
  • 1
  • 7
1
vote
2 answers

Doctrine - QueryBuilder - get youngest entity in result

I have this table structure: CREATE TABLE `inventory_item` ( `id` int(11) NOT NULL AUTO_INCREMENT, `articleID` int(11) NOT NULL, `quantity` int(11) NOT NULL, `date` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1…
Torben
  • 5,388
  • 12
  • 46
  • 78
1
vote
3 answers

order and group mysql

i am beginner in query mysql i have table rows and value like this enter image description here i want to select from the table : order by ID descending and group by Phone so result will be like this enter image description here pls any body help…
Defr
  • 13
  • 2
1
vote
4 answers

MySQL query select from multiple table depending on a certain condition

I have two tables as follow: table internetclient (id,full_name,location,phone_number) table internetclientdetails (incdid,icid,date_sub, date_exp,isPaid,profile_sub) the data in two table is as follow: client…
Ali
  • 1,633
  • 7
  • 35
  • 58
1
vote
3 answers

How to get only the first records from each day?

How to get only the first records from each day? id | name | date 1 | aaa | 2018-05-10 2 | bbb | 2018-05-10 3 | ccc | 2018-05-10 4 | ddd | 2018-05-11 5 | eee | 2018-05-11 6 | fff | 2018-05-13 I would like to receive: 1 | aaa |…
inail
  • 13
  • 2
1
vote
3 answers

Eliminate rows based on created timestamp

I would like some help in turning this table with different uuids for same emails and get only the row with min(timestamp), eliminating the other ones Sample data: UUID email created_timestamp 1 a@g.com 2017-05-01 2 …
Flavio de Falcao e Helena
  • 65
  • 8
1
vote
2 answers

By using SQLAlchemy, how to query the first record of everyday

I will use the table structure at This page class Book(Base): __tablename__ = "books" id = Column(Integer,Sequence('book_seq'),primary_key=True) name = Column(String(50)) time_added = Column(DateTime,…
Oliver.X
  • 13
  • 1
  • 4
1
vote
1 answer

Output top 3 most profitable products every quarter

I'm trying to output a top 3 products per quarter, that should be a total of 12 rows, since 3 top products per quarter. Closest output is the one provided below i have no idea how to like partition it every quarter SELECT * FROM (SELECT QUARTER,…
joas
  • 53
  • 4
1
vote
3 answers

Fetch the row which has the Max value for two column in MySql

i have a table which contains id , primaryid , data ,dataname columns i want only rows for which contains max id and primaryid create table #temp ( id int, primaryid int, data nvarchar(20), data_name nvarchar(30) ) insert…
Niteesh Kumar
  • 213
  • 3
  • 18
1
vote
3 answers

Select distinct id with max date according to department

I have a table such as following RevNo | RevContent | PIC | Created ------------------------------------------------------- 00 | Testing Purpose | Smith | 2008-01-11 01 | Testing Purpose | Windsor | 2008-02-01 02 |…
N.I.A
  • 75
  • 1
  • 7
1
vote
2 answers

Filtering Database Results to Top n Records for Each Value in a Lookup Column

Let's say I have two tables in my database. TABLE:Categories ID|CategoryName 01|CategoryA 02|CategoryB 03|CategoryC and a table that references the Categories and also has a column storing some random…
Jeffrey Kern
  • 2,024
  • 20
  • 40
1
vote
2 answers

How to join on _first_ record?

With my limited knowledge of complex mysql queries I'm trying to retrieve some information from a database. The story is this; users get an invite to come to our company. On the basis of this one invite, users can get multiple notifications and…
kramer65
  • 50,427
  • 120
  • 308
  • 488
1 2 3
…
99 100