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
1
vote
1 answer
MySQL groupwise max by the length of a field
Product table:
id: int
name: varchar(255)
info: varchar(8000)
I need to get a list of all products, grouped by name, and if some products have the same name then the one with the most data in info field should be picked to show. Another requirement…
Leo
- 1,016
- 1
- 13
- 32
0
votes
1 answer
mysql row_number() performance
Sorry. This is a rewritten question because there was no answer.
I changed it from rank() to row_number() because the same rank is not needed.
I'm currently working on a query, but I'm not getting the speed so I'm asking a question.
What I want to…
user10874312
0
votes
1 answer
Find maximum value of each group within a Pandas Frame
I do have a question, hoping that you could give me a little support. I looked into the archiv here, found a solution but that's taking much time and is not "beautiful", since works with Loops
Suppose you have a following frame
System Country_Key…
SMS
- 348
- 2
- 13
0
votes
2 answers
Get data from same row with MAX()?
[MariaDB 10.4, PHP8.0]
How can I rewrite this so it works so it get weight_date from the row with MAX(total_weight) and list it AS highest_weight_date? I have read that we can't use MAX() among with WHERE?
I have tested to rewrite several examples,…
bollob
- 27
- 7
0
votes
3 answers
return the value with the highest ID, of a predetermined set of IDs in mysql
I am attempting to pull the value associated with the highest detailID of a set of detailIDs that are predefined for each patientID.
here is an example of the table (also available as a fiddle):
CREATE TABLE `patient_details` (
patientId INT…
oeohfppe
- 3
- 2
0
votes
1 answer
Selecting rows with max value, in correlation to two other columns
I have a table with three columns: region, country, count.
I want to reduce my table to rows of region, country and count - where count is maximal among the region.
For example, if I have the following table:
region | country | count
asia | jo …
trizz
- 57
- 6
0
votes
1 answer
why is this sql not using index with date type column
mysql:5.7/8.0
table ddl
-- auto-generated definition
create table test_date_index
(
id int auto_increment primary key,
account_id int not null,
remark varchar(10) null,
cal_date date null,
constraint cal_date_index
unique…
DoubleFooker
- 29
- 4
0
votes
1 answer
For how many customers was this the first-ever merchant they ordered with? SQL question
I am struggling with this sql question:
For each merchant, find how many orders and first-time orders they had. First-time orders are meant from the perspective of a customer and are the first order that a customer ever made. In order words, for how…
milkeyyy
- 1
- 1
0
votes
2 answers
How to handle ties in SQL
SELECT
employee_id,
department_id,
first_name,
last_name,
hire_date,
job_id
FROM employees e
WHERE hire_date IN
(
SELECT max(hire_date)
FROM employees
WHERE e.department_id = department_id
…
PySquirrel
- 39
- 5
0
votes
1 answer
MYSQL How to fetch the first row before group by
I am trying to fetch products grouped by it's group code, but sorted by it's selling price.
When I run this query:
SELECT p.id, p.base_model, p.group_code, p.retail_price, p.selling_price, option_name
FROM product p
LEFT JOIN product_category pc ON…
jol123
- 97
- 2
- 12
0
votes
1 answer
tick data at specific time in daylight saving timezone
I have a Synology NAS mariadb has huge tick data. I want to create a table with price at 16:00 US/Eastern everyday.
It is easy to get the table with ticks at 10:05 GMT everyday without timezone consideration:
SELECT
TIMESTAMP,
…
Simon
- 411
- 6
- 11
0
votes
1 answer
how to know the behavior of MariaDB engine
I am new to MySQL/MariaDB.
I am comparing performance of a Stored Procedure in both MS SQL and MariaDB and found the performance of MariaDB to be slower than MS SQL. I found that the same query is taking more time in MariaDB than MSSQL. In fact i…
Saumik
- 23
- 5
0
votes
3 answers
Grouping By column and sort by
suppose we have 2 tables
@Table
export class Parent extends Model {
@PrimaryKey
@HasMany(() => child, { as: 'child', foreignKey: 'PARENT_ID' })
@Column
ID: number
@Column
NAME: string
....
}
@Table
export class Child extends Model{
…
Amir Shaneh
- 13
- 4
0
votes
1 answer
MySQL LEFT JOIN ON Closest Date
I am trying to create a daily point-in-time table by expanding a smaller table via a join with a Range table. The Range table defines the dates for which I want to create data. The metric table holds periodically persisted data. The tables look like…
Ron95
- 136
- 1
- 2
- 13
0
votes
2 answers
Optimize correlated subquery for order events table
I have a MariaDB table with order events:
EVENTID
ORDERID
DATA
TIMESTAMP
1
1
'stuff1'
2021-12-17 11:48:00.000
2
1
'newstuff1'
2021-12-17 11:49:00.000
3
1
'newerstuff1'
2021-12-17 11:49:30.000
4
2
'stuff2'
2021-12-17…
Daniel F.
- 23
- 2