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."

170 questions
1395
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 | ... …
Majid Fouladpour
  • 26,043
  • 19
  • 66
  • 124
157
votes
8 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,249
  • 2
  • 13
  • 19
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
  • 2,986
  • 6
  • 35
  • 55
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
  • 9,834
  • 5
  • 53
  • 77
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
  • 425
  • 1
  • 4
  • 11
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,270
  • 2
  • 6
  • 20
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,011
  • 28
  • 81
  • 137
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
  • 33
  • 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
  • 5,691
  • 9
  • 41
  • 65
3
votes
5 answers

Get the latest records per Group By SQL

I have the following table: ----------------------------------------------------------- ID oDate oName oItem oQty oRemarks ----------------------------------------------------------- 1 2016-01-01 A 001 2 …
Haminteu
  • 1,049
  • 1
  • 15
  • 37
3
votes
1 answer

Architecture of private chat

I want to do private chat service. Now I have a relation data base (PostgreSQL) for storing my messages and threads (threads - private rooms between users). I have following tables: 1) Message: id, text, datetime, sender_id, thread_id, is_read 2)…
Anton
  • 318
  • 2
  • 15
3
votes
1 answer

Matlab running max by group

In Matlab, how do I compute the running maximum of an array for each group (labeled by another array subs)? For example, think of the array subs as labels for 3 students, and the corresponding values in val as test scores, I want to compute the the…
Kakashi Hatake
  • 33
  • 3
3
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
  • 79
  • 9
3
votes
3 answers

How to find the distinct of one column based on other columns

I have a data frame like below col1 col2 col3 A Z 10 A Y 8 A Z 15 B X 11 B Z 7 C Y 10 D Z 11 D Y 14 D L 16 I have to select, for each…
Manoj G
  • 1,577
  • 2
  • 19
  • 27
3
votes
4 answers

Selecting distinct value from a column in MySql

Suppose,I have a table named items: sender_id receiver_id goods_id price 2 1 a1 1000 3 1 b2 2000 2 1 c1 5000 4 1 d1 700 2 1 b1 …
Parveez Ahmed
  • 1,159
  • 2
  • 14
  • 25
1
2 3
…
11 12