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
Get last record of each ID
I have this query :
SELECT Truck.Name AS name,
timestamp,
oil,
diesel,
cargo,
Truck.notes AS Remarks
FROM trip
INNER JOIN Truck USING (idTruck)
WHERE idTruck…
George Papas
- 15
- 4
1
vote
0 answers
mysql left join need order by and group by on right table
for simple, I write an example like this
select
a.xx,
a.xx,
... -- all table a's field
b.date,
b.money
c.date,
c.money,
d.date,
d.money,
e.date,
e.money
from
a
left join b on a.id = b.a_id
left join c on a.id = c.a_id
left join d on a.id =…
afraid.jpg
- 965
- 2
- 14
- 31
1
vote
2 answers
Select distinct on one column and return all other columns
I'm trying to find distinct orders based on the modified date. I want to sort it and find the latest modified order.
I tried to do as in postgreSQL.
select distinct on (orders) * from the table;
But this throws an error in case of mariaDB
Orders …
code gig
- 57
- 1
- 4
1
vote
5 answers
Improve sql code performance for a mariaDB query
I developed an application connected to mariaDB (Ver 15.1 Distrib 10.1.31-MariaDB, for Win32) in DelphiXE8.
I want to improve query performance.
Describe the simplified scenario:
de_User Table (innoDB) (rows 81762)
ID_U INT PRIMARY…
carmelocony
- 57
- 6
1
vote
3 answers
Select matching date to min() group by
I'm trying to select the lowest temperatures and it's related measure time grouped by weather station. It works fine, except that the measureTime column not matching the selected temperature. Someone who can help me out?
SELECT
…
jxe
- 310
- 4
- 16
1
vote
2 answers
How to optimize my mysql request to work with large database
I'm working on a project with php/mysql(mariaDB) that generates statistics with some data as input. The system that filled the DB is an electric powerplant and specifically the alarm system.
My DB has three fields: DeviceTime, VariableName and…
Cripsii
- 11
- 3
1
vote
4 answers
how to optimize sql when find the max record of each group while table is large?
I have a table which contains nearly 1 million+ records. I want to find the max record of each group.
Here is my sql:
SELECT *
FROM t
WHERE id IN (SELECT max(id) AS id
FROM t
WHERE a = 'some' AND b = 0
…
chaos
- 1,359
- 2
- 13
- 25
1
vote
1 answer
Finding max entries in postgresql table, sql
I have a table with several million entries. I want to find out when the maximum number of entries that occur within a given minute during a year(2018).
My initial strategy was to find out which month had the largest number of entries. Like…
MikeD
- 69
- 1
- 1
- 6
1
vote
1 answer
SQL : select minimum value AND other values in the row with the minimum
Using Oracle SQL, I want to select a group of people's ID, test date, and test results based on when the test was taken. My problem is that the order of priority is not in chronological order. I want to select a date in year 1. If they don't have it…
Whitney Kelley
- 13
- 3
1
vote
3 answers
How to select the latest records for distinct column values?
In my database I have profile ids with statuses which also have a timestamp. I want to have the latest status for each profile using a query. How can I do this the easiest way?
So f.e. for:
Id Profile Timestamp
-------------------------
1 …
Klyner
- 3,983
- 4
- 26
- 50
1
vote
0 answers
Alternative to window function in MariaDB 10.1
I have a windows function (over, partitioned by) in my code:
FROM (SELECT wp_posts.id,
wp_postmeta.post_id,
post_title,
post_type,
…
ptrcao
- 421
- 1
- 5
- 19
1
vote
0 answers
ORDER By not choosing the lower number out of 2 options
My purpose:
select entries with has_t "1" whenever it is there, if its not there, select entry with has_t "2".
All remaining entries for the relevant visit, should go inside "more_items" column
The issue:
The current code is first selecting the…
user8411456
- 347
- 1
- 5
- 18
1
vote
1 answer
mysql join subqueries
I have the following tables:
CREATE TABLE `data` (
`date_time` decimal(26,6) NOT NULL,
`channel_id` mediumint(8) unsigned NOT NULL,
`value` varchar(40) DEFAULT NULL,
`status` tinyint(3) unsigned DEFAULT NULL,
`connected` tinyint(1)…
Patrick
- 35
- 2
- 5
1
vote
1 answer
MySQL - Find most recent, best price for item from multiple vendors
I'm currently having trouble finding a solution for my problem and you guys are my last hope. Since two days I'm trying to solve this puzzle:
table item:
----------------------------
| id | item | customer |
----------------------------
| 1 |…
nim
- 35
- 4
1
vote
3 answers
Selecting entries with biggest value less than list of values
Suppose my table structure as follows:
id | Word
---|-----
1 | a
2 | aa
. | ..
I have a list of id's like this:
(...,900, 1000, 2000, 3000, 4000,....)
I want to find the biggest id less than each id in the above list.
My table id's is not…
Rezaeimh7
- 1,467
- 2
- 23
- 40