Questions tagged [greatest-n-per-group]
Use this for problems that involve returning one or more rows from each group in a data set. Despite the name, this tag is suitable for *minimum-* and *maximum-* per group queries.
358
questions
1
vote
0
answers
83
views
Moving from MySQL to MariaDB, slower performance with some query's
recenty we've have moved all of our server to new hardware and to MariaDB . I've installed a new server with VirtualMin for managing all of our company's applications. at a first glance most of the ...
- 11
0
votes
2
answers
53
views
How do I optimize this query that gets the newest entry of each group?
I have a query, that finds the newest item of each group based on the ID returned by a subquery. The problem is, that the table has a bit over 5000 entries and even with only this amount of entries ...
- 3
2
votes
3
answers
541
views
Conditional selection in a GROUP BY clause
I am an SQL beginner and ran into the following problem. Consider a table with data on countries, cities within that country and their population:
CREATE TABLE cities (
country VARCHAR(20),
...
- 121
0
votes
1
answer
56
views
The most recent rows in for each type/agent
I have a table (still on MySQL 5.7) with lots of data. This table is written by some scripts. It works like a "checkin", where each agent is checking in in some period of time. It looks like ...
- 13
-2
votes
1
answer
49
views
Oracle SQL - Selecting First Row for each ID
I am using Oracle SQL Developer and I am trying to pick the first entry of the student population.
So, that I have one row for each ID.
ID
DEGREE
AWARDED
ACADEMIC PERIOD
147
GR
MS
2022
147
UG
BS
...
1
vote
1
answer
34
views
Finding the latest status
I have 2 tables events and event_status.
The event_status table is the history table, and latest being the current one. The events and event_status have a one-to-many mapping.
I need a optimized query ...
0
votes
1
answer
84
views
How do I query out the largest value using an index
I have a table with several terabytes of event data in a very simple (id, bucket_id, data, created_at) schema and there is an index like so
create index index_events_on_created_at_and_bucket_id
on ...
- 157
0
votes
1
answer
288
views
Optimal way to get a total count of rows in a paged query in Postgres?
I need to improve the performance of a paged query for customer orders in a Type2 Postgres db (always insert a new record with a new ID, the newest ID is the current version of the record). Changing ...
- 207
0
votes
1
answer
193
views
How can I speed up my query that involves `max(timestamp)` in a huge table? I already added an index to every field
I have a huge table that has fields ip, mac, and timestamp.
Neither of the three fields is unique, but the combination of all three is.
Table is automatically populated, with newer records added all ...
- 385
1
vote
2
answers
74
views
Fetch latest of 2 fields(columns) from multiple records by most efficient way
I am trying to fetch the latest email and contact from multiple records. Each record has a date of creation. This row could or could not have an email/contact.
The table looks like this :
+------------...
- 13
0
votes
1
answer
98
views
Slow COUNT DISTINCT on a large table in PostgreSQL
In PostgreSQL 14, I have a table with around 10 million of rows with this structure:
CREATE TABLE search_stats (
id bigserial NOT NULL,
search_date timestamp NOT NULL,
user_id varchar NULL,...
- 1
0
votes
1
answer
553
views
Left Join with only one of multiple matches in the right table
I have a database, where every entry may have multiple names, therefore I made a name database. While all names will be displayed otherwhere I need a list of all main entries with one representative ...
- 1
-1
votes
1
answer
28
views
sql - how do I pick up all services that are open and latest of those ended - complicated
I am having issue capturing all rows that I need. A client can have number of services including same service again if that is an additional help, all services ended will have an end date and all open ...
- 1
3
votes
2
answers
1k
views
PostgreSQL equivalent to Oracle's ANY_VALUE(...) KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)
There's a technique in Oracle SQL that can be used to simplify aggregation queries:
Aggregate on a particular column, but get information from a different column, using a simple calculated column in ...
- 1,447
1
vote
2
answers
38
views
Combining Queries into 1 Query
I have 3 queries...
Query 1 Gives the Opening Balance at the Start of the Month
SELECT AccountBalance As 'OpenBalance', MONTHNAME(DateTime) as 'Month', MIN(DateTime) as 'Open'
FROM Table1
GROUP BY ...
- 43
0
votes
2
answers
70
views
Selecting last value to be entered each month
I am looking to pull a closing balance from the database for each month.
I have tried
SELECT CloseBal As 'Balance', MONTHNAME(DateTime) as 'Month', DateTime
FROM Table1
WHERE MAX(DateTime)
Group By ...
- 43
1
vote
1
answer
55
views
Getting ID when sorting by joined table using group also
I have small query to getting products based on variants (colors, materials), like:
SELECT
ANY_VALUE(id) AS id,
parent_id,
COUNT(id),
color,
material,
MIN(price) AS priceMin
FROM
(
...
- 123
2
votes
1
answer
37
views
How to get the most recent result sets when grouping key and date are in separate tables
Good evening! I'm having some difficulty. I'm trying to obtain the most recent row from a few tables using SQL. I can do this using a loop in code, but it requires a lot of round trips to the database ...
- 121
2
votes
1
answer
52
views
How to retrieve grouped records with specified last record columns
id
user_id
category
subcategory
1
243
credit
purchase
2
243
credit
renewal
3
243
credit
purchase
4
243
credit
renewal
5
243
revenue
recognition
6
245
credit
purchase
7
245
credit
renewal
8
248
...
- 121
1
vote
1
answer
87
views
Return each product with the earliest instance of its latest change
I'm trying to find the earliest created_at date of the latest category for all products. I've attached a sample table below:
CREATE TABLE IF NOT EXISTS `products` (
`id` int(6) UNSIGNED NOT NULL,
`...
5
votes
3
answers
442
views
Can Postgres scan indexes backwards?
We use an Amazon RDS instance with
PostgreSQL 11.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
I have a simple classic top-1-per-group query. I need to ...
- 4,577
1
vote
0
answers
45
views
How do I show only the value of the first orderdetail in MySQL
MYSQL version 5.7.38-google-log
I have orders and orderdetails table. Follow this https://dbfiddle.uk/bdosg8aa
SAMPLE DATA
CREATE TABLE orders (
orderID int,
orderAmount int );
INSERT INTO orders(...
- 11
0
votes
0
answers
59
views
selecting the date to be used in calculating percentage change
I have a table named stock_info
with the following columns: dt, stock, close_price.
I would like to make a query that would calculate the rolling three month price change for each stock. The previous ...
- 21
0
votes
0
answers
37
views
Get only three rows for each type value
Here is my query:
SELECT su.userid AS uid,
su.display_name AS username,
su.user_type,
Ifnull(a.totalinvites, 0) AS invites,
...
- 121
0
votes
0
answers
22
views
Apply comparision within group of rows to get one row per group [duplicate]
I have a table ordered by fruit and date resulting from a join such as the following:
+--------+------------+
| fruit | date |
+--------+------------+
| apple | 2002-02-02 |
| apple | 2003-03-...
- 103
-1
votes
2
answers
87
views
How to write query to select highest value from same date
please help me write this query for MySQL.
I want to select highest value from same datetime. So if there are multiple equal datetime value, return only one row with highest price.
For summary, one ...
- 83
0
votes
1
answer
167
views
How to join two tables A and B and have the most recent status from B?
I have two tables: A and B
A
provider_id
date
111
date
222
date
333
date
B
provider_id
status
date
111
initialized
date
111
released
date
222
failed
date
The result I want
provider_id
...
- 103
1
vote
0
answers
172
views
Return back only top N rows for each ID - SQL
I have written this SQL after browsing different topics. The topic I got the solution from -> Limit Records
MySQL version: 5.6
SELECT op.*
FROM (select op.*,
(@rn := if(@s = order_id, @...
- 11
0
votes
1
answer
46
views
counting minimum sent_time for everyday
Postgres 14
db fiddle updated - Link
EDIT:
It seems like I need 1 more row:
CREATED AT - specifies when conversation was created.
SELECT DISTINCT ON (date_trunc('day', sent_time), conv_id, user_id)
...
- 119
-1
votes
1
answer
106
views
Getting first messages only for every day in a specific date range
Table messages:
conv_id
user_id
content
sent_time
1
001
1st_msg
01-01-1990 00:00:00
2
002
2nd_msg
02-01-1990 00:00:00
How do we select the first message and the first reply sent in a conversation (...
- 119
2
votes
2
answers
793
views
How to remove duplicates and sort in a subquery?
I have records with a "title" column that I am splitting up by space and performing a full text search with each word. I am storing the result in a materialized view.
This works, but I get ...
- 123
2
votes
1
answer
395
views
Previous Row based on double where condition and group max sum
I have such a table and I would like to get these values:
+───────────────────+────────+──────────+──────────────────────────────────────────────────────+
| TS | Value | ValueA | ...
- 123
1
vote
1
answer
152
views
Query hierarchical file permissions
I have a table representing some files with their paths and another table with file sharings by user and files.
Below is Table files, simplified. Example:
I have a directory A (path /A) containing ...
- 13
0
votes
1
answer
140
views
Find the youngest customer grouped by province
SELECT a.province, c.birth_date, c.name
FROM customer c
JOIN address a ON (c.cust_id = a.cust_id)
GROUP BY a.province
ORDER BY birth_date DESC;
I want to find the youngest customer in each ...
- 11
2
votes
1
answer
701
views
Backfilling data using generate time series
I have a table like this:
Date
Group name
value
2022-01-01
A
1.0
2022-01-15
A
0.5
2022-01-31
A
0.2
but this contains only 3 days. I need a table with full-calendar dates, a row for every day of ...
- 73
0
votes
1
answer
73
views
interpreting an explain statement for a slow query on MySQL 5.7.36
I have a question about some performance problems we are having that are a bit beyond me. When we do this query on about 2M reviews, it takes about 20 seconds but seems like it should be hitting ...
- 1
0
votes
2
answers
48
views
Add Foreign Key to the Table as a column
I have two tables as below:
Table name: table_1
ID
studentId
Date
enrolled
parent name
parents annual income
1
12345
2022/01/13 14:39:15
enrolled
ABC
12345
2
12345
2022/01/13 14:39:06
not-enrolled
...
0
votes
0
answers
39
views
Get top users with user type by amount
i have tried to find top retailer and top distributor with below query.
SELECT
(SELECT SUM(amount) AS total_amount
FROM transactions tm
LEFT JOIN user_master um ON um.p_id = tm.user_id
...
- 101
0
votes
1
answer
302
views
Pick specific column value in a group of rows based on condition
I am working with mariadb server version 10.6.4.
This is my table definition:
CREATE TABLE `tmp_dba` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`case_id` bigint(20) DEFAULT NULL,
`client_id` ...
- 15
0
votes
2
answers
1k
views
Merge duplicates on condition
I have the following table in a PostgreSQL db
id | value_1 | value_2
-----------------------------
1 | 100 | 10
1 | NULL | 20
2 | 100 | 20
3 | 100 | 12
4 | 100 | 16
5 | NULL ...
- 111
0
votes
1
answer
2k
views
ORDER BY datetime makes the query very slow
I have Visitor Management System - time spent at work by specyfic user.
When I add ORDER BY a added_on it return the results after at least 18 seconds (even though there is pagination (32 per page)) ...
1
vote
2
answers
3k
views
How to get the second highest value per group?
Getting the second highest value from a table has been solved many times, but I'm looking for the second highest value in each group.
Given this table:
+----+-----+
| A | 10 |
| A | 20 |
| A | ...
- 606
0
votes
1
answer
57
views
How to get the maxium row value for a similar values
In my table below, I have id and job_id fields with some of the job_id fields being duplicates.
I want to get the MAXIMUM value of the id field corresponding to each unique job_id.
I have now tried ...
- 105
1
vote
2
answers
2k
views
Delete old rows from a big table, keeping one per given time interval
I am trying to delete some rows from a table in which I have saved timestamps only (data type timestamptz):
The usual operation to delete from DB/table is very simple such as I want to delete all ...
- 281
3
votes
1
answer
138
views
How do I aggregate related rows when there's no group key?
I'm using MariaDB 10.6 and have a table:
CREATE TABLE velocities (
`state` int(8) NOT NULL,
`timestamp` datetime NOT NULL,
`velocity` decimal(5,2) NOT NULL,
`name` varchar(15) NOT NULL
) ...
- 133
1
vote
1
answer
3k
views
SELECT rows from table one by one, comparing one column values with values in an array
I have an array of timestamps in a PLPGSQL code block: timestamp_array.
For each element of timestamp_array I want to get the element itself (timestamp_element) and some columns from a table (my_table)...
- 287
1
vote
1
answer
3k
views
Group by latest date with conditions
I need your help!
Let's say I have this table:
Instance
Date
MetricID
Value
Marc
09/14/21
1
5
Marc
09/14/21
2
2
Marc
09/14/21
3
1
John
09/14/21
1
10
John
09/14/21
2
1
John
09/14/21
3
1
Marc
09/...
- 21
0
votes
1
answer
1k
views
SELECTing n rows for each GROUP with WHERE condition
I SELECT n rows for each ArticleID from TagMap table
SELECT a.ArticleID,a.TagID FROM TagMap a
LEFT JOIN TagMap b ON a.ArticleID = b.ArticleID AND a.TagID < b.TagID
GROUP BY a.ArticleID,a.TagID
...
- 4,491
3
votes
2
answers
511
views
Apply nth occurrence number on each distinct value in a column
I have a table with a column of values where each value occurs a variable number of times (i.e., one value may occur 1 time, and another value may occur 3 times). I need to add a column that ...
- 133
0
votes
1
answer
5k
views
Joining only one row of a table?
I have this query which is supposed to find the latest uploaded pids and reps of a user. The query looks like this:
SELECT usr.id, up.*,
(SELECT COALESCE(state, 'processing') AS pidState FROM ...
