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
2 answers
MySQL grouping and getting newest rows
I would like to get newest row in column , But i need to get them just before grouping them each other. If i put Order by after group by, mysql ordering them which they before grouped.
What i tried to do, grouping messages for user's last messages…
Onder OZCAN
- 1,556
- 1
- 16
- 36
0
votes
2 answers
Max function issue with the values from other columns
I have the following MySQL query :
Select match_static_id, comments as last_comment, max(timestamp)
from comments as c
group by match_static_id;
I have table for comment on matches and i want to have the latest comment of each match.
so i use the…
Basel
- 1,305
- 7
- 25
- 34
0
votes
2 answers
How to group by one column but select last value
This is my database for messages
I want to group by receiver_id and this is my SQL
SELECT * FROM messages WHERE sender_id=2
ORDER BY created_at DESC
GROUP BY receiver_id
HAVING COUNT(receiver_id)>=1
It's working but it always shows subject Bok…
FosAvance
- 2,363
- 9
- 36
- 52
0
votes
0 answers
Duplicate values for nodes - caused by left join?
I am having a MySQL query created by Drupal Views - it is returning duplicate values. As far as I can tell, this is because there is a one to many relationship between the nodes table and the registrations, and whereas the selection of the node…
Steven Matthews
- 9,705
- 45
- 126
- 232
0
votes
0 answers
how to get column of latest row mysql
The query below is fine with me except the content in column message_content is of the first row in table al_messages. How can I get the content of the latest row?
SELECT a.CONVERSATION_ID,
a.CREATOR_ID,
c.SCREEN_NAME AS…
complez
- 7,882
- 11
- 48
- 56
0
votes
2 answers
MYSQL inline view query (top customer)
I try to make a query, so that I can see who is the top customer in a month (every month since begin till now).
Now I have the tables:
orders (orderID, orderdate, customerID, Netamount, tax, totalamount)
orderline (orderlineID, orderID, prodID,…
JOP
- 25
- 1
- 4
- 9
0
votes
1 answer
MySQL groupwise maximum performance issues on a million row table
I am trying to find a straightforward way to improve the performance for very active forums where there are a huge number of posts and mysql can no longer do table sorts in memory and doesn't seem to take full advantage of indexes.
This simple query…
ck_
- 3,353
- 5
- 31
- 33
0
votes
1 answer
Facebook like conversations list in CakePHP 2.1 (Related to group-wise maximum, sub-query, join)
I have the following MySQL tables:
CREATE TABLE IF NOT EXISTS `conversations` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user1_id` int(11) NOT NULL,
`user2_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user1_id_2`…
hswner
- 582
- 1
- 4
- 17
-1
votes
2 answers
Show only the max id record outa of a GROUPED BY list
(Simplified example). I have this table:
USER ID | code | sales
1 x 100
1 y 200
1 z 150
2 x 300
2 z 17
2 y 100
For each user, I…
Itay Moav -Malimovka
- 52,579
- 61
- 190
- 278
-1
votes
1 answer
Most popular item name that buyers order on their first purchase
Let's say that I have a table with a DateTime column, purchase_time and other order details(store_id, buyer_id, item_id, value) and I am trying to find the most popular item name that buyers order on their first purchase?
So far I am here, how do I…
Sharma Singh
- 7
- 1
-1
votes
1 answer
Last Successful Payment Date
How I can join the below two tables and get only the LastSucessfull Payment Date from the Transactions Table? I want to pull only LastSucesfullPaymentdates, which should also consider through the returns;
Business Rules for LastSucesfull Payment…
Shekar Nandi
- 17
- 4
-1
votes
1 answer
MySQL 8 DISTINCT with greatest ID
Hopefully this is an easy problem, and I'm just thinking about it wrong.
I have an association between id_1 and id_2. There is a 1->n relationship between id_1 and id_2. Namely, if no id_2 exists for id_1 it has a default value of -1. This can look…
Alex
- 2,145
- 6
- 36
- 72
-1
votes
1 answer
MySQL query - Select statement from two tables with group by returning records with largest ids
I really need help from you, I've spend a lot of time already on trying to figure it out but without success :(
I have two tables:
What I need is to group everything by sea_id / bat_season and gain the greatest Id's for these seasons. So bat_id's…
Prometheus
- 3
- 2
-1
votes
1 answer
How to optimize join query?
The sql I finally use is like:
SELECT
t1.account_id,
t1.system_value,
t1.date,
acs.account_status
FROM
(
SELECT
account.id,
account.account_id,
system_value,
`date`
FROM
account,
…
jia Jimmy
- 1,693
- 2
- 18
- 38
-1
votes
1 answer
Slow Querying DB
I am currently optimising a system with many connected tables.
The part that I am working on right now is displaying table orders. The problem is that in this table there are also many relations (around 10) which I am querying. The problem itself is…
Lukas Grofcik
- 457
- 1
- 3
- 15