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."
Questions tagged [groupwise-maximum]
212 questions
0
votes
1 answer
Want one value per id from multiple value per id
I have a dataframe in which one ID has multiple value assign. But I want one ID and one value which is highest
Given dataset
Id. Class
111. Metro
111. Urban
111. Rural
222. …
Bikash Jha
- 31
- 1
- 4
0
votes
1 answer
Optimizing a query for loading message history in a chat app
I have 2 tables, which are a users table, and a messages table
`users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(35) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM AUTO_INCREMENT=859312…
XtevensChannel
- 91
- 8
0
votes
1 answer
Slow query performance in MySQL
I would like to find out what causes the slow execution of my MySQL query here. It's 1 row fetched in 0.0017s (3.6215s) How can i optimize this?
SELECT hadmlog.hpercode as 'HOSPITAL NUMBER', FLOOR (hadmlog.patage) as 'AGE', CONCAT (hperson.patlast,…
J.szareyo
- 25
- 8
0
votes
1 answer
SQL selecting the rows with the maximum value in a table ordered by a column from another
I have 2 tables which, simplified, look like this:
Name Server_id score
-----------------------------
John 1 300
John 2 400
Mary 2 321
John 1 100
Mary 1 …
CuriousCharlie
- 17
- 5
0
votes
1 answer
Which is the best performance settings to perfom a triple join query
Hi everybody I'm working on a personal project but right now i have almost everything done, except for this, i have 2…
manolix
- 1
- 1
0
votes
1 answer
My SQL with Python: Select the row with the highest value and change the value there
I have already searched for several solutions here and tried to get a working code. Everything works except for the where query.
In the where query I search for the highest value (numeric). However, this does not really work...
Here is my code and…
Max73
- 1
0
votes
0 answers
SQL groupwise maximum query with multiple tables
Groupwise maximum queries are clearly harder than one would expect.
I've found there are a number of approaches to this, but all that I've found seem to be based on a single table, and I've not been able to work out how it should work when based on…
ChrisV
- 8,748
- 3
- 48
- 38
0
votes
1 answer
How to resolve this sql script! The problem is that I need to get the newest record from a table column,But it is too slow. The Sql below:
Can someone help me to optimize this SQL? I am really appreciating it.
SELECT
gssur.id,
date( gssur.create_time ) time,
gssur.store_id,
gssur.store_name,
gssur.goods_id,
gssur.spu_code,
gssur.sku_code,
…
Allen
- 88
- 6
0
votes
3 answers
MySQL event-based support ticket system
Afternoon all.
I've recently been tasked with working on an event-based support ticket system, but I've encountered a lot of issues and I think the problem is the database structure.
At the moment it looks a bit like this:
create table tickets (
…
SystemParadox
- 8,203
- 5
- 49
- 57
0
votes
2 answers
MySQL - select 2nd lowest value by ID
I have a fairly large dataset called offers containing around 7m rows.
The table has 30 columns but I'm only using two of them, cap_id - a unique identifier for a vehicle, and price - the monthly cost to lease the vehicle.
I want to write a query…
Alex Midmore
- 47
- 7
0
votes
2 answers
MYSQL query slow SELECT DISTINCT
I want to output the most used search items of my classifieds of the last 3 days
I have an extra table where I save all search items together with the day and a counter which increments if the search is used again (and for future use the category…
Alexander Dobernig
- 753
- 6
- 21
0
votes
5 answers
How to select records based on the max value of two fields?
Given the following simple table:
+-----+-------------+---------+----+
| id_ | match_op_id | version | p1 |
+-----+-------------+---------+----+
| 1 | 1 | 1 | 1 |
| 2 | 1 | 1 | 5 |
| 3 | 1 | 2…
Jossy
- 589
- 2
- 12
- 36
0
votes
0 answers
Get Profit by last Purchase's price with Sale's Price based on Product Id, sold on Date
I'm trying to optimize my current query which is calculating profit based on last purchase's net amount, depending upon product's selling date and quantity in sale's table. Currently, it's consuming too much memory and time.
Spacking is an item's…
Aman
- 11
- 2
0
votes
1 answer
How can I removes duplicates by using MAX and SUM per group identifier?
I'm creating an open order report using SQL to query data from AWS Redshift.
My current table has duplicates (same order, ln, and subln…
Maysie Hewitt
- 1
- 2
0
votes
0 answers
Only sorting by order entered, not by timestamp
I cannot ORDER BY the dt_contacted field. No matter which way I have done this, it keeps on listing in the order it was entered by.
SELECT leads.*, historical.history
FROM leads
LEFT JOIN (SELECT lead_id,
group_concat(…
Scott Weisberg
- 53
- 1
- 6