Stack Overflow
Stack Overflow

Questions tagged [groupwise-maximum]

Questions regarding the common SQL problem selecting records holding the group-wise maximum of a certain column. Example: "For each article, find the dealer or dealers with the most expensive price."

212 questions
1620
votes
27 answers

SQL select only rows with max value on a column

I have this table for documents (simplified version here): id rev content 1 1 ... 2 1 ... 1 2 ... 1 3 ... How do I select one row per id and only the greatest rev? With the above data, the result should contain two rows: [1, 3,…
Majid Fouladpour
  • 29,356
  • 21
  • 76
  • 127
1308
votes
34 answers

Retrieving the last record in each group - MySQL

There is a table messages that contains data as shown below: Id Name Other_Columns ------------------------- 1 A A_data_1 2 A A_data_2 3 A A_data_3 4 B B_data_1 5 B B_data_2 6 C C_data_1 If…
Vijay Dev
  • 26,966
  • 21
  • 76
  • 96
195
votes
9 answers

LEFT JOIN only first row

I read many threads about getting only the first row of a left join, but, for some reason, this does not work for me. Here is my structure (simplified of course) Feeds id | title | content ---------------------- 1 | Feed 1 | ... Artists artist_id…
KddC
  • 2,853
  • 2
  • 17
  • 19
11
votes
4 answers

How to find the record in a table that contains the maximum value?

Although this question looks simple, it is kind of tricky. Consider the following table: CREATE TABLE A ( id INT, value FLOAT, "date" DATETIME, group VARCHAR(50) ); I would like to obtain the ID and value of the records that contain the…
Jonas
  • 1,365
  • 3
  • 21
  • 39
9
votes
6 answers

select rows with largest value of variable within a group in r

a.2<-sample(1:10,100,replace=T) b.2<-sample(1:100,100,replace=T) a.3<-data.frame(a.2,b.2) r<-sapply(split(a.3,a.2),function(x) which.max(x$b.2)) a.3[r,] returns the list index, not the index for the entire data.frame Im trying to return the…
Misha
  • 3,114
  • 8
  • 39
  • 60
8
votes
4 answers

Optimize groupwise maximum query

select * from records where id in ( select max(id) from records group by option_id ) This query works fine even on millions of rows. However as you can see from the result of explain statement: QUERY…
nurettin
  • 11,090
  • 5
  • 65
  • 85
5
votes
7 answers

SQL : get Min and Max value in one column

I have an employee table with a name and a salary. I want to print these 2 columns with only 2 records in them, the name of my highest and lowest payed employee. It should look something like this: Name Salary ------------------ James …
Tsunami
  • 121
  • 1
  • 2
  • 7
5
votes
5 answers

Get the latest records per Group By SQL

I have the following table: CREATE TABLE orders ( id INT PRIMARY KEY IDENTITY, oDate DATE NOT NULL, oName VARCHAR(32) NOT NULL, oItem INT, oQty INT -- ... ); INSERT INTO orders VALUES (1, '2016-01-01', 'A', 1, 2), (2,…
Haminteu
  • 1,292
  • 4
  • 23
  • 49
5
votes
2 answers

Finding running maximum by group

I need to find a running maximum of a variable by group using R. The variable is sorted by time within group using df[order(df$group, df$time),]. My variable has some NA's but I can deal with it by replacing them with zeros for this…
olga
  • 99
  • 9
5
votes
2 answers

MySQL Join Without Duplicates

I have a customers table, and an orders table. Each customer can have many orders. I want to select every customer, along with their earliest order number from the orders table (it is important that I select the earliest order number, not just any…
Tyler Durden
  • 317
  • 3
  • 4
  • 11
4
votes
2 answers

SQL - return latest of multiple records from large data set

Background I have a stock_price table that stores historical intra-day stock prices for roughly 1000 stocks. Although the old data is purged regularly, the table regularly has 5M+ records. Structure is loosely: | id | stock_id | value | change…
itwasluck3
  • 443
  • 1
  • 5
  • 12
4
votes
4 answers

MariaDB 10.3.18 : How to get 2 records with random and distinct value?

There is a MySQL table named stat: line_name device_name count 1001 3548001 2 1002 3548002 3 1003 3548003 6 2001 3548004 7 2002 3548005 3 2003 3548006 4 3001 3548007 3 3002 3548008 9 3003 3548009 7 I need to…
harp1814
  • 1,494
  • 3
  • 13
  • 31
4
votes
2 answers

Can I safely use two MAX calls in a SQL query

I have a table with several hundred thousand entries and I'm trying to use a query to get a result set for a specific receiver_id and group them by sender_id. My current SQL query works but I want to know if there could be any potential problems…
Paul
  • 11,671
  • 32
  • 91
  • 143
3
votes
1 answer

Filter duplicated value in SQL

I'm trying to build a query that provides me a list of five jobs for a weekly promotion. The query works fine and gives the right result. There is only one factor that needs a filter. We want to promote different jobs of different companies. The…
Gertjan Gijsbers
  • 43
  • 3
3
votes
3 answers

Query to rank rows in groups

I'm using Apache Derby 10.10. I have a list of participants and would like to calculate their rank in their country, like this: | Country | Participant | Points | country_rank…
Fidel
  • 7,027
  • 11
  • 57
  • 81
1
2 3
…
14 15