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
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
- 420
- 5
- 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
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,776
- 2
- 24
- 29
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,325
- 4
- 17
- 28
3
votes
6 answers
get all second highest from a mysql table
I have a table with four fields as follows,
(UID is User ID)
ID UID MUSIC DATE
1 0 a 2013-10-20
2 0 a 2013-10-21
3 0 a 2013-10-22
4 0 a 2013-10-24
5 0 b 2013-10-11
8 0 b 2013-10-15
10 0 c 2013-10-26
9 0 …
SuHun Han
- 65
- 7
2
votes
3 answers
MySQL Select where in array of statuses and last occurrence equals specific value
Let's say I have a table like this:
id user_id status updateded_id
1 1 yes 2/5/2013
2 2 no 2/1/2013
3 3 yes 1/28/2013
4 2 yes 1/24/2013
5 2 no 1/20/2013
6 1 yes 1/16/2013
7 …
matisa
- 497
- 3
- 25
2
votes
2 answers
CakePHP 4 - Query to find the latest status from another table
I'm using CakePHP 4 to build an application that shows an inventory of documents and the latest status for each document.
The tables are fairly simple:
documents: This contains a list of documents, each of which has a unique id, a human-friendly…
Andy
- 5,142
- 11
- 58
- 131
2
votes
3 answers
Laravel: Limit only 1 record from the 2nd table using eloquent join
I have a two tables colors and color_translations with structure like this:
colors
id
created_at
updated_at
1
2021-08-25
NULL
2
2021-09-01
NULL
color_translations
id
color_id …
jojo
- 126
- 11
2
votes
1 answer
Selecting Names with Highest Aggregate in each Class
I'm new to SQL and I'm working with the following tables:
CREATE TABLE Student (
StudentID int,
Name nvarchar(30),
Class nvarchar(30)
);
INSERT INTO Student (StudentID, Name, Class)
VALUES (1,'Alfredo','X'), (2,'Jack','X'), (3,'Chris','Y'),…
The Singularity
- 2,428
- 3
- 19
- 48
2
votes
2 answers
Get most recent result from a LEFT JOIN column
I'm creating a custom forum from scratch and I'm attempting to use some LEFT JOIN queries to get information such as total posts, total threads and most recent thread. I've managed to get the data but the recent thread keeps returning a random value…
GenesisBits
- 364
- 2
- 23
2
votes
4 answers
SQL: How to exclude maximum if another column doesn't match
I'm using Oracle 10g.
My research at
SQL - How to select a row having a column with max value and
http://jan.kneschke.de/projects/mysql/groupwise-max/
address what to do with max(row) and then finding other max(rows)
But, I'm not there yet.…
lamarz
- 35
- 1
- 5
2
votes
3 answers
Get DateTime corresponding to the lowest Time
I'd like to get the Date & ID which corresponds to the lowest and Largest Time, respectively the extreme rows in the table below with ID 5 & 4.
Please note the following:
Dates are stored as values in ms
The ID reflects the Order By Date ASC
Below…
Enissay
- 4,969
- 3
- 29
- 56
2
votes
1 answer
Fetching Rows By Minimum Scores From MySQL Database
I have a database which records golf scores by people. Each person has arbitrarily many scores as there is no hard limit to games of golf.
Along with each Person and Score, the Date (stored as Unix Time but here shown normally) and some other data…
royco
- 41
- 4
2
votes
2 answers
Compare scores in two tables and show those where the most recent is different
I have two tables in MariaDB where I need to show those in the left table where their current score is not the same as the most recent score in the history table.
For example:
users
id name current_score
1 Bob 4
2 Tom 5
3 Fred 3
4 …
bhttoan
- 2,641
- 5
- 42
- 71
2
votes
4 answers
How to return only one row from the right-most table using mysql join
I have two tables. I want to join them in a way that only one record(the first matched record) in the right table is returned for the record in the left most table. the two tables have a one to many relationship. here are the tables below:
events…
Ewomazino Ukah
- 2,286
- 4
- 24
- 40