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 - Convert rows into columns based on latest date and list of ids for each column
I still can't figure this out even after hours of searching and trying. Maybe I did not choose the right words?
Okay, so I have table with events like this:
EVENT
+----+------------+------------+------+
| id | id_article | id_section | date…
David Pocar
- 23
- 4
1
vote
1 answer
Maximum sum in array such that atmost 2 consecutive in 5 elements can be selected
I can't work around how to select the elements.
for example if we have 1,2,3,4,5,6,7,8,9,10
and we choose 4,5
then we cant choose 6,7,8 but we can choose 9th
So, I guess in general
If we choose 2 consecutive elements arr[i] and arr[i+1],
then we…
Guest99318
- 11
- 2
1
vote
2 answers
Select all entries that match the oldest entry
In MariaDB / MySQL I have a table like this:
table (key1, key2, date) with INDEX(key1, key2)
Now I want to take the oldest entry:
SELECT * FROM `table` ORDER BY `date` ASC LIMIT 1
And finally all entries matching key1 and key2 from the first…
user2015253
- 1,263
- 4
- 14
- 25
1
vote
1 answer
how to find the exact location of maximum value from data frame in Python 3.5- modified
I have one DataFrame in Python 3.5, such as:
In [1]:tway5new.info()
Index: 44 entries, to VOI
Columns: 43802 entries, 2011-01-01 00:00:00 to 2015-12-31 23:00:00
dtypes: int64(43802)
memory usage: 14.7+…
TWord
- 41
- 5
1
vote
2 answers
How to get unique value after removing space and group by some different column in postgres
I have a table like
id type note
1 A ab cd
1 B cdef
1 A abd
1 A abcd
I want to get all the unique notes after removing space so my result will be like this
id type note
1 A ab cd
1 A abd
1 B cdef
because after removing…
Sabyasachi Ghosh
- 2,765
- 22
- 33
1
vote
2 answers
Pivot in MySQL - Show first and last values depending on datetime-column
after hours of researching and trying out myself I finally registered on Stackoverflow, which helped me for years by just reading. Thank you very much for this learning opportunity! I now hope to find help with a my MySQL-problem I can't get solved.…
Kangaroo
- 13
- 2
1
vote
1 answer
Groupwise maximum in larger query
Really struggling with a query that uses groupwise maximum, any help would be much appreciated. Feel free to point out if I should not be using groupwise maximum.
I have two tables application and email, one application can have many emails. What…
haakym
- 12,050
- 12
- 70
- 98
1
vote
1 answer
Laravel get most recent date of joined table
I'm performing this query with Laravel's query builder with a left join with the two tables linking on the column s_nid. With the table I'm left joining to conference_report there are many rows with one s_nid and I only want to get back the row with…
haakym
- 12,050
- 12
- 70
- 98
1
vote
2 answers
SQL - obtain latest record in subquery subset or in join using GROUP BY
I've been puzzling over this for some time now and realise its time to ask for some help. As part of an integration with a third party system, I have introduced an additional table to an existing system to manage synchronization of records. I'll…
richhallstoke
- 1,519
- 2
- 16
- 29
1
vote
2 answers
mysql select latest timestamp in a group
I have a table for which I would like to select the most recent time stamp in a category defined by the value of a particular column in the table.
Specifically
SELECT *
FROM takelist
WHERE producer_name = 'sean'
AND bucket_id =…
user3145007
- 11
- 1
- 2
1
vote
2 answers
SQL - MAX function returning incorrect data
I am trying to get with each question that having max seq_no record, but it is returning wrong data.
Here is query:
SELECT id, attempt, seq_number, question
FROM question_states
WHERE attempt = 374
AND seq_number IN ( SELECT MAX(seq_number) FROM…
Suleman Ahmad
- 2,025
- 4
- 28
- 43
1
vote
2 answers
Mysql get current highest priced items
I'm looking to get the 20 highest current priced products.
I have a product table that has many prices table.
products: id
prices: id, product_id, value, change_date
Here's an example.
Product: 1
price: 11, change_date 12/1/2013, value: $10
…
alaup
- 61
- 1
- 8
1
vote
2 answers
PDO Return highest integer value and no duplicate entries
I am not sure if this question has been asked yet but I don't know how to word it better :)
I have a table with game results. I need to return the top 10 results (for a leaderboard) but my results has to return the highest game per player and not…
mauzilla
- 3,574
- 10
- 50
- 86
1
vote
3 answers
How to SELECT 2 tbls but only 1 row from second tbl with lowest PK in MySQL?
structure:
tbl 1
|car_id(PK)| make | model | year |
-----------------------------------
| 1 | Toyot | Camry | 1999 |
| 2 | Honda | Civic | 2005 |
tbl 2
|img_id(PK)| car_id| img_link |
------------------------------------
| 1…
Tatarin
- 1,238
- 11
- 28
1
vote
3 answers
Select records with highest values for each subset
I have a set of records of which some, but not all, have a 'path' field, and all have a 'value' field. I wish to select only those which either do not have a path, or have the largest value of all the records with a particular path.
That is, given…
MrTheWalrus
- 9,670
- 2
- 42
- 66